Dear,
i create Stord procured with the below dynamic sql but when create smart object select columns not appear in smart object.
ALTER PROCEDURE Rdbo].oSp_General_GetDynamicMenu]
@TableName NVARCHAR(100),
@ColumnID NVARCHAR(100),
@ColumnName NVARCHAR(100),
@ColumnParentID NVARCHAR(100),
@ColumnURL NVARCHAR(100) =NULL,
@ColumnIcon NVARCHAR(100)=NULL,
@ColumnOrder NVARCHAR(100)=NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DynamicSQL NVARCHAR(MAX)
IF NOT EXISTS ( SELECT 1 FROM sys.tables WHERE name = @TableName ) BEGIN RAISERROR('Invalid table name.', 16, 1) RETURN END
DECLARE @Final TABLE (
ID INT, Name NVARCHAR(MAX), ParentID INT, IsParent bit DEFAULT(1), CURL] NVARCHAR(100) ,Icon NVARCHAR(100), OrderID int
)
create TABLE #FinalTabel (
ID INT, Name NVARCHAR(MAX), ParentID INT, IsParent bit DEFAULT(1), IURL] NVARCHAR(100) ,Icon NVARCHAR(100), OrderID int
)
SET @DynamicSQL = N' INSERT INTO #FinalTabel SELECT '+@ColumnID+',' +@ColumnName+','+@ColumnParentID+', 1 ,'+@ColumnURL +' , '+@ColumnIcon+',' +@ColumnOrder+' FROM ' + QUOTENAME(@TableName) +
'
;WITH RecursiveCTE AS (
SELECT ID, Name, ParentID, IsParent, Icon, URL, OrderID, 1 AS Level
FROM #FinalTabel
WHERE ParentID IS NULL
UNION ALL
SELECT t.ID, t.Name, t.ParentID, t.IsParent, t.Icon, t.URL, t.OrderID, Level + 1 AS Level
FROM #FinalTabel t
INNER JOIN RecursiveCTE r ON t.ParentID = r.ID
)
INSERT INTO #FinalTabel
SELECT ID , Name, ParentID, IsParent, Icon, URL, OrderID
FROM RecursiveCTE
ORDER BY COALESCE(ParentID, ID), ParentID, ID'
EXEC sp_executesql @DynamicSQL’
INSERT INTO @Final SELECT * FROM #FinalTabel
SELECT * FROM @FINAL