Creating and Selecting table variables dynamically in SQL Server stored procedure?

Question

Please guide me how to create table variables dynamically. I know it can be like this:

DECLARE @people TABLE 
( 
    id INT, 
    name VARCHAR(32) 
);

How I will create if dont know columns and data types. Scenario is I have to create table variable as per a physical tables and selct data into them, use them in SP and then return data in table variables to C# program.

For example I have a table Employees. I want create a table variable with same structure as Employyes have. But mentioning columns take a lot time (as Employees have about 100 columns)

Please advice.

Answer 1

So here is how you can build the DECLARE TABLE statement dynamically:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';


SELECT @sql = @sql + ',
' + c.name + ' ' + t.name
 + CASE
  WHEN t.name LIKE '%char' OR t.name LIKE '%binary' THEN
   '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE
   CONVERT(VARCHAR(4), c.max_length/CASE WHEN t.name LIKE 'n%'
   THEN 2 ELSE 1 END) END + ')'
  WHEN t.name IN ('float') THEN
      '(' + CONVERT(VARCHAR(4), c.precision) + ')'
  WHEN t.name IN ('decimal', 'numeric') THEN
   '(' + CONVERT(VARCHAR(4), c.precision) + ','
   + CONVERT(VARCHAR(4), c.scale) + ')'
  ELSE ''
 END
 FROM sys.columns AS c
 INNER JOIN sys.types AS t
 ON c.system_type_id = t.system_type_id
 AND c.user_type_id = t.user_type_id
 WHERE c.[object_id] = OBJECT_ID('dbo.Employees')
 ORDER BY c.column_id;

SET @sql = 'DECLARE @people TABLE (' + STUFF(@sql, 1, 1, '') + '
);';

SELECT @sql;

But now what? You can't insert into it from outside the scope of dynamic SQL:

EXEC sp_executesql @sql;
INSERT @people(id, name) SELECT 1,'foo';

Yields this error:

Msg 1087, Level 15, State 2, ...
Must declare the table variable "@people".

Once again, a scoping issue - @people only exists in the dynamic SQL and ceases to exist as soon as it's finished. So while you could proceed and append to the @sql variable:

SET @sql = @sql + 'INSERT @people ...; SELECT id, name FROM @people;';

...this will get out of hand very quickly. And I still have no idea how your C# code can be aware of all the columns and data types involved, but it's too hard to write the SQL to do so... you know you can drag the columns node from Object Explorer onto a query window and it will produce a nice comma-separated list of column names for you, right?

Answer 2

You can query the system tables in order to find the names and types of the columns that a table consists of. See this question and its answers.

You can then take this information and generate scripts that will create the table value types.

This will not help on the client side, however, as it will need to know about the types and values of the data returned.