none
Dynamic SQL and Table Variables (Declaration Issues)

    Question

  • I have created the DSQL code below, I'm using a CTE and Merge together. My issue is storing and retrieving the RowCount of OUTPUT $ACTION.  I've learned that you can't use table variables inside Stored Procs (which is what I'm using now). The SP will be used in the Execute SQL Task of SSIS.

    Does anyone have any suggestions as to work around this?....thank you soooo much in advance:

    ALTER PROCEDURE [dbo].[usp_MergeInsertSQL_STG_INT_CSV_Source] 
    -- Add the parameters for the stored procedure here
    @TableName AS nvarchar(100),
    @DatabaseNameLocal AS nvarchar(100),
    @PrimaryKey AS nvarchar(100)

    AS
    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- DECLARE A TABLE WHERE YOU CAN DERIVE ACTION TYPES AND RECORD COUNTS
    DECLARE @SUMMARY_OF_CHANGES TABLE (CHANGE_TYPE VARCHAR(20))

    DECLARE @LogTableName AS nvarchar(255)
    DECLARE @SQL_Statement AS nvarchar(max)
    DECLARE @SQL_Statement1 AS nvarchar(max)
    DECLARE @Final AS nvarchar(max)

    SET @LogTableName = 'LOG_INT_' + @TableName
     

    SET @SQL_Statement1 = '/* <<<I1>>> CTE for STAGING Table*/'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'WITH CTE_STAGING AS'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + '('
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'SELECT'

    SELECT @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + ' s.' + COLUMN_NAME + ','
    FROM 
     INFORMATION_SCHEMA.COLUMNS
    WHERE 
     TABLE_NAME = @LogTableName
     AND COLUMN_NAME NOT IN
    (
    'INSERT_DATE',
    'LAST_MOD_DATE',
    'VALID_FROM_LOG_DATE',
    'VALID_TO_LOG_DATE',
    -- The fields below is in LOG_INT_DIM_CONTRACT and is not being used
    'OLDVAL',
    -- The fields below is used in OLDER log files and is no longer being used
    'LOG_DATE'
    )

    SET @SQL_Statement1 = LEFT(@SQL_Statement1,LEN(@SQL_Statement1)-1) + CHAR(10) + 'FROM'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + ' ' + @DatabaseNameLocal + '_stg..STG_' + @TableName + ' s'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'INNER JOIN'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + '(SELECT'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'max(row_num)ROW_NUM, ' + @PrimaryKey
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'FROM'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + ' ' + @DatabaseNameLocal + '_stg..STG_' + @TableName + ' b'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'GROUP BY b.' + @PrimaryKey + ') b'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 'ON'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + 's.ROW_NUM = b.ROW_NUM'
    SET @SQL_Statement1 = @SQL_Statement1 + CHAR(10) + ')'

    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ''
    SET @SQL_Statement = 'MERGE ' + @DatabaseNameLocal + '_int..INT_' + @TableName + ' AS i'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'USING CTE_STAGING AS s'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'ON'
    SELECT @SQL_Statement = @SQL_Statement + CHAR(10) + '(s.' + ccu.COLUMN_NAME + ' = i.' + ccu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name 
    JOIN INFORMATION_SCHEMA.COLUMNS c ON ccu.TABLE_NAME = c.TABLE_NAME AND ccu.COLUMN_NAME = c.COLUMN_NAME
    WHERE tc.CONSTRAINT_TYPE = 'Primary Key' and ccu.COLUMN_NAME NOT IN ('VALID_FROM_LOG_DATE', 'FILE_NAME') and ccu.TABLE_NAME = @LogTableName
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ')'

    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'WHEN NOT MATCHED'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'THEN INSERT'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + '('
    SELECT @SQL_Statement = @SQL_Statement + CHAR(10) + ' ' + COLUMN_NAME + ','
    FROM 
     INFORMATION_SCHEMA.COLUMNS
    WHERE 
     TABLE_NAME = @LogTableName
     AND COLUMN_NAME NOT IN
    (
    'INSERT_DATE',
    'LAST_MOD_DATE',
    'HASH_VALUE',
    'VALID_FROM_LOG_DATE',
    'VALID_TO_LOG_DATE',
    'FILE_NAME',
    -- The fields below is in LOG_INT_DIM_CONTRACT and is not being used
    'OLDVAL',
    -- The fields below is used in OLDER log files and is no longer being used
    'LOG_DATE'
    )

     
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' INSERT_DATE,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' LAST_MOD_DATE,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' HASH_VALUE,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' FILE_NAME,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' PROCESS_FLAG'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ') '
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'VALUES'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + '('
    SELECT @SQL_Statement = @SQL_Statement + CHAR(10) + ' s.' + COLUMN_NAME + ','
    FROM 
     INFORMATION_SCHEMA.COLUMNS
    WHERE 
     TABLE_NAME = @LogTableName
     AND COLUMN_NAME NOT IN
    (
    'INSERT_DATE',
    'LAST_MOD_DATE',
    'HASH_VALUE',
    'VALID_FROM_LOG_DATE',
    'VALID_TO_LOG_DATE',
    'FILE_NAME',
    -- The fields below is in LOG_INT_DIM_CONTRACT and is not being used
    'OLDVAL',
    -- The fields below is used in OLDER log files and is no longer being used
    'LOG_DATE'
    )
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' GETDATE(),'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' GETDATE(),'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' s.HASH_VALUE,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' s.FILE_NAME,'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ' 1'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ')'


    -- CAPTURE ACTION TYPES AND RECORD COUNTS
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'OUTPUT $ACTION INTO @SUMMARY_OF_CHANGES;'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ''

    -- 'INSERT COUNT' TO POPULATE VARIABLE IN SSIS
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'SELECT CHANGE_TYPE'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'FROM @SUMMARY_OF_CHANGES'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'WHERE CHANGE_TYPE = ''INSERT'''
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'GROUP BY CHANGE_TYPE;'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ''
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'SELECT @@ROWCOUNT AS INSERTED_RECORDS;'

    SET @Final = @SQL_Statement1+' '+@SQL_Statement
    EXEC sp_executesql @Final

    PRINT @SQL_Statement1+' '+@SQL_Statement


    END

    GO

    EXEC [dbo].[usp_MergeUpdateSQL_STG_INT_CSV_Source] 'DIM_BUSINESS_PARTNER_T','GCS','BPARTNER'

    Wednesday, September 11, 2013 11:10 PM

Answers

  • A table variable can used inside a stored procedure and the scope is limited to that procedure only.

    If a child Proc is called , it cant access the table variable inside it. In that case you use temp tables.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 1:00 AM
  • Hi there,

    If you want to use the table variable as part of the dynamic script, then it need to be defined as part of the dynamic statement. In your sp you need to create as part of @SQL_Statement1 and pass the whole @SQL_Statement1 to exec sp_executeSQL.

    For example: 

    declare @SQL nvarchar( 100 ) = N'INSERT INTO @TBL VALUES(1)';
    
    declare @TBL table(
                       a int );
    
    exec sp_executesql @SQL;
    
    /**************************************
    Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable "@TBL".
    **************************************/
    
    go
    
    declare @SQL nvarchar( 100 ) = N'DECLARE @TBL TABLE (a INT);INSERT INTO @TBL VALUES(1)';
    exec sp_executesql @SQL;
    
    /******************
    (1 row(s) affected)
    ******************/
    
    go


    Regards Harsh

    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:00 PM
    Thursday, September 12, 2013 4:16 AM
  • The child process (dynamic SQL execution) "sees" the #temptable created in the parent process.

    It is NOT TRUE vice versa.

    Therefore if communications necessary between parent a child, you have to create a #temptable first or use permanent table.

    Dynamic SQL example:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, September 12, 2013 4:44 AM
    Moderator
  • Your procedure does nothing with the table variable other than populate it with rows.  So why bother?  If your procedure is still under development and you DO plan to do something with those rows after executing your dynamic sql, then you will need to follow Kalman's suggestion.  Your local variables (table or scalar) cannot be seen in your dynamic query since it does not execute in the same scope.
    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:00 PM
    Thursday, September 12, 2013 1:17 PM
  • Declare your dynamic SQL like this:

     declare @sql nvarchar(max) = N'
     SELECT CHANGE_TYPE, COUNT(*) AS CHANGE_TYPE_COUNT
     FROM #SUMMARY_OF_CHANGES
     GROUP BY CHANGE_TYPE;
     '

    It's much easier to read.  Also use a single batch for the MERGE and subsequent SELECT.  That makes it much easier to debug.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:01 PM
    Thursday, September 12, 2013 6:25 PM

All replies

  • A table variable can used inside a stored procedure and the scope is limited to that procedure only.

    If a child Proc is called , it cant access the table variable inside it. In that case you use temp tables.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 1:00 AM
  • Whenever I execute the Stored Proc I get the error that I need to Declare @SUMMARY_OF_CHANGES, yet its declared in the script. 
    Thursday, September 12, 2013 3:40 AM
  • Hi there,

    If you want to use the table variable as part of the dynamic script, then it need to be defined as part of the dynamic statement. In your sp you need to create as part of @SQL_Statement1 and pass the whole @SQL_Statement1 to exec sp_executeSQL.

    For example: 

    declare @SQL nvarchar( 100 ) = N'INSERT INTO @TBL VALUES(1)';
    
    declare @TBL table(
                       a int );
    
    exec sp_executesql @SQL;
    
    /**************************************
    Msg 1087, Level 15, State 2, Line 1
    Must declare the table variable "@TBL".
    **************************************/
    
    go
    
    declare @SQL nvarchar( 100 ) = N'DECLARE @TBL TABLE (a INT);INSERT INTO @TBL VALUES(1)';
    exec sp_executesql @SQL;
    
    /******************
    (1 row(s) affected)
    ******************/
    
    go


    Regards Harsh

    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:00 PM
    Thursday, September 12, 2013 4:16 AM
  • The child process (dynamic SQL execution) "sees" the #temptable created in the parent process.

    It is NOT TRUE vice versa.

    Therefore if communications necessary between parent a child, you have to create a #temptable first or use permanent table.

    Dynamic SQL example:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, September 12, 2013 4:44 AM
    Moderator
  • Your procedure does nothing with the table variable other than populate it with rows.  So why bother?  If your procedure is still under development and you DO plan to do something with those rows after executing your dynamic sql, then you will need to follow Kalman's suggestion.  Your local variables (table or scalar) cannot be seen in your dynamic query since it does not execute in the same scope.
    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:00 PM
    Thursday, September 12, 2013 1:17 PM
  • to Scott_Morris-ga:

    I am using the rowcount to populate a variable in an SSIS package.

    Thursday, September 12, 2013 4:11 PM
  • I decided to use the TempTable, which eliminated the declare @summary_of_changes error.

    BUT now I'm not getting count results from OUTPUT $ACTION. I replace part of the above script with this:

    -- CAPTURE ACTION TYPES AND RECORD COUNTS
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'OUTPUT $ACTION INTO #SUMMARY_OF_CHANGES;' 
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + ''

    -- 'INSERT COUNT' TO POPULATE VARIABLE IN SSIS
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'SELECT ''INSERT'' AS CHANGE_TYPE, COUNT(*) AS CHANGE_TYPE_COUNT'
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'FROM #SUMMARY_OF_CHANGES' 
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'WHERE CHANGE_TYPE = ''INSERT'''
    SET @SQL_Statement = @SQL_Statement + CHAR(10) + 'GROUP BY CHANGE_TYPE;'

    Could you help me figure out why I'm not getting any results from the above?

    Thursday, September 12, 2013 5:43 PM
  • Declare your dynamic SQL like this:

     declare @sql nvarchar(max) = N'
     SELECT CHANGE_TYPE, COUNT(*) AS CHANGE_TYPE_COUNT
     FROM #SUMMARY_OF_CHANGES
     GROUP BY CHANGE_TYPE;
     '

    It's much easier to read.  Also use a single batch for the MERGE and subsequent SELECT.  That makes it much easier to debug.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Rookie3000 Thursday, September 12, 2013 7:01 PM
    Thursday, September 12, 2013 6:25 PM
  • I want to personally thank everyone for their input and help. I finally figured it out. 

    Until the next time....


    Thursday, September 12, 2013 7:01 PM