在 SQL Server 存储过程中动态创建和选择表变量?

2021-09-10 00:00:00 sql tsql sql-server

请指导我如何动态创建表变量.我知道它可以是这样的:

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

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

如果不知道列和数据类型，我将如何创建.场景是我必须根据物理表创建表变量并将数据选择到其中，在 SP 中使用它们，然后将表变量中的数据返回到 C# 程序.

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.

例如我有一张员工表.我想创建一个与 Employyes 具有相同结构的表变量.但是提到列需要很多时间(因为员工大约有 100 列)

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)

请指教.

推荐答案

这里是如何动态构建 DECLARE TABLE 语句的方法:

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;

但是现在呢?你不能从动态 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';

产生此错误:

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

再一次，一个范围问题 - @people 只存在于动态 SQL 中，一旦完成就不再存在.因此，虽然您可以继续并附加到 @sql 变量:

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

...这会很快失控.而且我仍然不知道您的 C# 代码如何知道所涉及的所有列和数据类型，但是编写 SQL 来做到这一点太难了……您知道可以将列节点从对象资源管理器拖到查询上窗口，它会为您生成一个很好的逗号分隔的列名列表，对吗?

...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?

相关文章