T-SQL Recipes Table Variables and temporary Tables and CTE

Problem many times, we want table variables to execute in dynamic SQL, but the reality is very bony. such as this example:

DECLARE @sql_command NVARCHAR(MAX);DECLARE @parameter_list NVARCHAR(MAX);DECLARE @last_names TABLE(last_nameNVARCHAR( -) );SELECT  @sql_command = 'Select Distinctfirstnamefrom person.personwhere LastName in (select Last_Name from @last_names)'EXECsp_executesql@sql_command;

View Code

Some people see here, perhaps the first instinct will ask, why not in dynamic SQL declaration @last_names, in fact, and eggs.

This road is not going to work, let's change the way, for example, type Table:

CREATETYPE last_name_table as TABLE(last_nameNVARCHAR( -));GO--DROP TYPE last_name_tableDECLARE @sql_command NVARCHAR(MAX);DECLARE @parameter_list NVARCHAR(MAX);DECLARE @first_name_calling_sql NVARCHAR( -)= 'Edward';DECLARE @last_names  aslast_name_table;SELECT  @sql_command = 'INSERT into @last_names (last_name) SELECT LastName from Person.person WHERE FirstName = @first_name_calling_sql; Select Distinctfirstnamefrom person.personwhere LastName in (select Last_Name from @last_names)'SELECT  @parameter_list = '@first_name_calling_sql NVARCHAR, @last_names last_name_table READONLY'EXECsp_executesql@sql_command,@parameter_list,@first_name_calling_sql,@last_names;

View Code

If the INSERT statement is taken out, of course, can be executed, but some business is to deal with the dynamic, amortization hands ...

T-SQL Recipes Table Variables and temporary Tables and CTE