Why I can't use template table in dynamic query SQL SERVER 2005

Answer 1

the variable @table is out of scope. You would either have to create a global temp table ##table or declare the table within the dynamic SQL.

If you intend on using dynamic SQL I suggest you read this excellent article.

http://www.sommarskog.se/dynamic_sql.html

Answer 2

You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Try this may work for you:

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

create TABLE #table 
(
    ID1 varchar(30),
    ID2 int
)

INSERT INTO #table values(1, 1);
INSERT INTO #table values(1, 2);
INSERT INTO #table values(1, 3);



DECLARE @field varchar(30);
SET @field = 'ID1'

SET @SQLString = N'SELECT * FROM #table WHERE  @fld  = 1';
SET @ParmDefinition = N'@fld varchar(30)';

DECLARE @query varchar(MAX);
SET @query = 'SELECT * FROM #table WHERE ' + @field + ' = 1'
EXEC (@query)

drop table #table