Dynamic SQL

[abenitez77]

When I try to execute a command below in this script it works but when i change it to being dynamic as I did below, it does not work. Why does that part not work?

Declare @Schema as varchar(100)
Declare @cmd as varchar(400)
Declare @TableNameHeader as varchar(400)
Declare @TableNameDetail as varchar(400)
Declare @SourceDB as varchar(100)
DECLARE @MyTableVar TABLE
(
TableID INT,
TableName VARCHAR(100)
)

set @SourceDB = 'NJ_Toys_Claims'
set @Schema = @SourceDB + '.INFORMATION_SCHEMA.COLUMNS'
set @TableNameHeader = 'TRU_2010_PRICING_CLAIM_SUMMARY'
set @TableNameDetail = 'TRU_2010_PRICING_CLAIM_DETAIL2'

-- Add Tables Info for Header
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select @TableNameHeader, 2, 0 WHERE @TableNameHeader
Not IN (Select Tablename From tblTables)

-- Add Tables Info for Detail
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select Item, 2, 0 From PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@TableNameDetail,',') as T WHERE T.Item
Not IN (Select Tablename From tblTables)

-- This below works....
/*
INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM @MyTableVar as t
INNER JOIN NJ_Toys_Claims.INFORMATION_SCHEMA.COLUMNS as c
ON c.TABLE_NAME = t.TableName
*/

-- This below does not work...
Set @cmd = 'INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM @MyTableVar as t
INNER JOIN ' + @Schema + ' as c
ON c.TABLE_NAME = t.TableName'

exec(@cmd)

select @cmd

 

[abenitez77]

I get this error msg:
Must declare the table variable "@MyTableVar".

 

[gmmastros]

When you use the exec command, the dynamic sql that it actually executes is run in a separate process. This separate process cannot see the table variable because it was created in a different process.

If you switch to using a temp table instead, your code will work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[abenitez77]

changed to using a temp table and that worked!
Select * Into ##tmpTableInfo From @MyTableVar

Set @cmd = 'INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM ##tmpTableInfo as t
INNER JOIN ' + @Schema + ' as c
ON c.TABLE_NAME = t.TableName'

exec(@cmd)