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'

Answer 1

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.

Answer 2

Whenever I execute the Stored Proc I get the error that I need to Declare @SUMMARY_OF_CHANGES, yet its declared in the script. 

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

to Scott_Morris-ga:

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

Answer 7

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?

Answer 8

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/

Answer 9

I want to personally thank everyone for their input and help. I finally figured it out. 

Until the next time....