Dynamic sql using table variable -TSQL

Question

My problem is using the table variable in 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') 

He says that he should declare the scalar variable "@temp" when the temp table is declared, I tried to use the original temp table and it worked, but I had problems trying to update my model.So entity, is there any solution this problem

Note: I have to use exec because in the filters I store the string for the where clause.

Answer 1

For the solution, I had to use a temporary table, and then at the beginning of my stored procedure, I used the if condition from EF, I could not get the return scheme from the Stored procedure by selecting #temp table anwser.

This is the best solution for this scenario, I think.

Answer 2

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