Question

dynamic sql query not working

  • 31 July 2023
  • 4 replies
  • 183 views
Akhanjer
Badge +5

Dear,

 

i create Stord procured with the below dynamic sql but when create smart object select columns not appear in smart object.

 

 

ALTER PROCEDURE [dbo].[Sp_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), [URL] NVARCHAR(100) ,Icon  NVARCHAR(100), OrderID int 
   
    )

    create TABLE  #FinalTabel  (

        ID INT,  Name NVARCHAR(MAX), ParentID INT, IsParent bit DEFAULT(1), [URL] 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

4 replies

P
Userlevel 3
Badge +9

Hi there,

You cannot use dynamic sql inside of your stored procedure as the service instance in that case is not able to discover the properties that are supposed to be returned.

You’ll need to rewrite your SP so that it doesn’t use dynamic queries.

Have a read of this:  https://cdn-cms.k2.com/kbs/files/7349/K2%20SQL%20Server%20Service%20Broker%20Stored%20Procedure%20Requirements.pdf

Cheers

P
Userlevel 3
Badge +9

So it turns out you can’t call out to sp_executesql and get any results back from it - the below sproc works as it is, but uncomment the stuff below and it will no longer return results:

 

CREATE PROCEDURE dbo.TestDynamic
	@TableName NVARCHAR(100)
AS
BEGIN
	-- This will work
	DECLARE @Temp TABLE (TableName NVARCHAR(100), Id UNIQUEIDENTIFIER)

	INSERT INTO @Temp (TableName, Id) VALUES (CONCAT(@TableName, ' 1'), NEWID())

	-- The below won't work as it calls out to sp_executesql 
	--CREATE TABLE #Temp (TableName NVARCHAR(100), Id UNIQUEIDENTIFIER)
	--DECLARE @DynamicSql NVARCHAR(MAX)
	--SET @DynamicSql = 'INSERT INTO #Temp (TableName, Id) VALUES (CONCAT(''' + @TableName + ''', '' 1''), NEWID())'

	--EXEC sp_executesql @DynamicSql

	--INSERT INTO @Temp SELECT * FROM #Temp

	SELECT * FROM @Temp
END

GO

EXEC dbo.TestDynamic 'Test'

So what you’ll have to do if it needs to be dynamic is to use a cursor to do the equivalent of what the dynamic sql is doing to get round the use of it.

Cheers 

Akhanjer
Badge +5

hi @paulk ,

but when delete this statment 


    

    INSERT INTO @Final SELECT * FROM #FinalTabel

 

stored procedure return column successfully  

 

 

 

MillaZ
Userlevel 5
Badge +20

Hi @Akhanjer 

Have you resolved this question? 

Check out these Nintex University Learning paths: https://community.nintex.com/p/NintexUniversity

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings