Declaring and Consuming User Defined Table Types in SQL

Question

I am trying to use User Defined Table Types in SQL, but am hitting problems I didn't expect. Relevant sections of code are as follows:

declare @customer varchar(10), @prodgroup varchar(10)

SET @customer = 'CUST123'
SET @prodgroup = 'AA9A3'

declare @pctn  prices_ctn --User defined table type
declare @ppck  prices_pck --User defined table type
declare @psin  prices_sin --User defined table type

declare @sqlctn varchar(8000), @sqlpck varchar(8000), @sqlsin varchar(8000)
SET @sqlctn = 'insert into @pctn select product_code, price FROM pricing_table with (nolock) WHERE price_list  = ''CTN'' AND product_code like ''' + @prodgroup + ''''
SET @sqlpck = 'insert into @ppck select product_code, price FROM pricing_table with (nolock) WHERE price_list  = ''PCK'' AND product_code like ''' + @prodgroup + ''''
SET @sqlsin = 'insert into @psin select product_code, price FROM pricing_table with (nolock) WHERE price_list  = ''SIN'' AND product_code like ''' + @prodgroup + ''''

exec(@sqlctn)
exec(@sqlpck)
exec(@sqlsin)

However, when I execute the code, I am getting 'Must declare the table variable' error for each of my user-defined table types.

Table types are all created and show in 'User-Defined Table Types' in SSMS. SQL version is 12.0.2000.8.

What am I doing wrong?

Answer 1

A table variable cannot be used in a dynamic SQL query because it only exists in the current session just like any other variable.

EXEC creates a second session where none of you variables exist unless you pass them as a parameter.

In those queries, the table variables are out of the dynamic SQL scope.

Table type and variable are used to pass a table to a stored procedure (READ ONLY table) or get a table from a procedure or function.

There are 2 ways to make it work:

• Insert into

  declare @params = N'@prodgroup varchar(10)';
  SET @sqlctn = 'select product_code, price FROM pricing_table with (nolock) WHERE price_list  = ''CTN'' AND product_code like @prodgroup; '
  
  declare @pctn  prices_ctn --User defined table type
  insert into @pctn  
  exec sp_executesql @sqlctn, @params, @prodgroup = @prodgroup;
  

• Temp Table

  create table  #pctn  (...)
  declare @params = N'@prodgroup varchar(10)';
  SET @sqlctn = 'insert into #pctn select product_code, price FROM pricing_table with (nolock) WHERE price_list  = ''CTN'' AND product_code like @prodgroup '
  exec sp_executesql @sqlctn, @params, @prodgroup = @prodgroup;
  

Note that I am using sp_executesql and a paramter variable. This is the best way to call dynamic SQL. EXEC alone and string concatenation for parameters should be avoided.