Dynamic sql using table variable -TSQL

Question

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.

Use #temp instead of @temp and just reference #temp directly in the EXEC() instead of treating it like a variable name. — Aaron Bertrand
– Aaron Bertrand, Commented May 20, 2013 at 16:33
@AaronBertrand i made the query functional like that, but i had problems when updating my entity model so i am afraid i cant use that fix. — Aleks
– Aleks, Commented May 20, 2013 at 16:55
I have the exact problem when using temp table. stackoverflow.com/questions/16593473/… — Aleks
– Aleks, Commented May 20, 2013 at 17:36
Wow, EF is pretty annoyingly intrusive, eh? In theory, it should be able to determine the output of the stored procedure without caring what comes before the first select. Out of curiosity, what happens if you encrypt the stored procedure? — Aaron Bertrand
– Aaron Bertrand, Commented May 20, 2013 at 19:07

Magnus · Accepted Answer · 2013-05-20 17:23:46Z

1

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')

edited May 20, 2013 at 17:23

answered May 20, 2013 at 16:39

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aleks Over a year ago

This is a solution that works, but i cant use this as a solution it slows the query down significantly.

Magnus Over a year ago

@Aleks Why do you need a temp table anyway, why not join with the sub-query select itm_id from Tblitm where itm_name not like ''%aa%'' directly instead?

Aleks Over a year ago

the query i wrote is an example i have a lot more complicated query and when i put it all in exec it takes a lot more time than using a temp table.

Community · Accepted Answer · 2017-05-23 12:23:16Z

0

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.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 21, 2013 at 8:56

Aleks

3,9763 gold badges26 silver badges29 bronze badges

Collectives™ on Stack Overflow

Dynamic sql using table variable -TSQL

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related