CODEWITHSUNDEEP
sqlsql-servert-sql
Aleks
Aleks

Reputation: 3966

Dynamic sql using table variable -TSQL

My problem is using a table variable in a exec.

declare @sort_col nvarchar(1000) = 'itm_id'
declare @sort_dir nvarchar(4) = 'desc'
declare @filters nvarchar(1000) = ' and itm_name like ''%aa%'''

declare @temp table
(
 itm_id int
)

insert into @temp
EXEC('select itm_id from Tblitm where itm_name not like ''%aa%''')

EXEC('select * from (select (ROW_NUMBER() OVER (ORDER BY '+@sort_col+' '+@sort_dir+')) row_num, * FROM (select itm_id, itm_name, 
dbo.fnItmsHistory(itm_id) itm_history
         from dbo.Tblitm as itm
         left outer join '+@temp+' as temp on itm.itm_id = temp.itm_id
         where itm_id=itm_id and temp.itm_id = null '+@filters+') as x) as tmp')

It says Must declare the scalar variable "@temp" when the temp table is declared i tried using original temp table and it worked, but i had problems when trying to update my entity model.So is there any solution for this problem?

Note: I must use exec because in filters i store string for the where clause.

Upvotes: 6

Views: 9436

Answers (2)

Aleks
Aleks

Reputation: 3966

For solution i had to use a temp table and then on the start of my stored procedure i used the if condition from the EF can't infer return schema from Stored Procedure selecting from a #temp table anwser.

It's the best solution for this scenario i think.

Upvotes: 0

Magnus
Magnus

Reputation: 46997

Try moving the table variable inside the dynamic statement.

EXEC('
declare @temp table
(
 itm_id int
)
insert into @temp
select itm_id from Tblitm where itm_name not like ''%aa%''
select * from (select (ROW_NUMBER() OVER (ORDER BY '+@sort_col+' '+@sort_dir+')) row_num, * FROM (select itm_id, itm_name, 
dbo.fnItmsHistory(itm_id) itm_history
         from dbo.Tblitm as itm
         left outer join @temp as temp on itm.itm_id = temp.itm_id
         where itm_id=itm_id and temp.itm_id = null '+@filters+') as x) as tmp')

Upvotes: 1

Related Questions