SQL Server - How can I use a "Table" variable in a dynamic sql query?

Asked By Burak Gunay on 17-Jan-07 02:16 PM

hi,

this is what i have in my stored procedure

declare @SQL varchar(6000)
declare @TableVar table
(
ID int IDENTITY PRIMARY KEY,
Crs_Id int
)

set @SQL = 'INSERT INTO @TableVar(Crs_ID) values(2)'
exec(@SQL)

when i run this, i get a "Must declare the variable '@TableVar'." error

it seems you can only use table variables in explicit sql calls like

declare @SQL varchar(6000)
declare @TableVar table
(
ID int IDENTITY PRIMARY KEY,
Crs_Id int
)

INSERT INTO @TableVar(Crs_ID) values(2).. this works

but when you try to use them in dynamic queries, you run into problems

how can i use a table variable in a dynamic sql query?

Response - F Cali replied to Burak Gunay on 17-Jan-07 02:28 PM

You cannot access a local table variable in a dynamic sql query because the local variable is only visible within the scope of that script. Dynamic sql is another scope. What you can do is make use of global temporary tables, those temp tables that begins with two # signs.

SQL Server Helper
http://www.sql-server-helper.com/

this is my problem - Burak Gunay replied to F Cali on 17-Jan-07 02:46 PM

Hello,

I am executing a query as follows

set @SELECT = @SELECT + ', lin,lindesc '
set @SELECT = @SELECT + ', max(qty) as Qty, sum(Usage) as Usage, sum(C1) as C1,sum(C2) as C2,sum(C3) as C3,sum(C4) as C4 '

set @SQL = @SELECT + ' from #TempEquip group by ' + @GROUP1 + @GROUP2

exec ( @SQL)

then I'd like to modify the resultant data set as follows

sSql = "Update <result> set C1 = Format(C1 / qty, '##,##0.0'),C2 = Format(C2 / qty, '##,##0.0'),C3 = Format(C3 / qty, '##,##0.0'),C4 = Format(C4 / qty, '##,##0.0') where qty <> '0'"

exec(sSql)

sSql = "Update <result> set C1 = 0,C2 = 0,C3 = 0,C4 = 0 where qty = '0'"

exec(sSql)

how can i do this without saving the result of the first execution into a temp table? i am already pulling data from #TempEquip table, i don't want to create another one. that's why i thought i could use a table variable here, but it looks like it can't be used in dynamic queries.

any other ideas?

You can try to create the table variable - Robbe Morris replied to Burak Gunay on 17-Jan-07 03:29 PM

in the same string as your dynamic sql. Ron is right, you are creating the table variable outside the scope of EXEC. If you include the create table syntax in the dynamic sql, I suspect it will work just fine.

ok this worked - Burak Gunay replied to Robbe Morris on 17-Jan-07 03:42 PM

declare @SQL varchar(6000)
set @SQL = 'declare @TableVar table
(
ID int IDENTITY PRIMARY KEY,
Crs_Id int
)'
set @SQL = @SQL + 'INSERT INTO @TableVar select crs_id from crs '

set @SQL = @SQL + ' update @TableVar set crs_id = 9999 where id = 1'
set @SQL = @SQL + 'select * from @TableVar '
exec(@SQL)

thanks

Rob replied to Burak Gunay on 09-Mar-11 04:55 PM

You must create a table type first and use that in your table variable declaration within your dynamic sql.

For example:

SET @Sql = N' SELECT ItemID,'

+ N' RTRIM(Title) As ''Title'','

+ N' FriendsHit'

+ N' FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @Sort + N') As RowNumber'

+ N' FROM @Search)'

+ N' As ResultSet'

+ N' WHERE RowNumber BETWEEN (@CurrentPage-1)*@PageSize+1 AND @CurrentPage*@PageSize'

EXEC sp_executesql @Sql,

N'@Search As SearchTableType READONLY, @CurrentPage int, @PageSize int',

@Search = @Search,

@CurrentPage = @CurrentPage,

@PageSize = @PageSize

Needless to say your SearchTableType is declared previously as such:



-- Declare table type



CREATE TYPE SearchTableType AS TABLE
(
  ID int primary key identity(1,1),
  ItemID bigint NULL,
  Title varchar(100) NULL,
  FriendsHit varchar(max),
  Ranking bigint NULL
)





-- Declare your table var inside your SP




DECLARE @Search As SearchTableType

Now you can go ahead and populate your table variable and pass it to your dynamic sql happily :-P

HTH