問題描述

請指導我如何動態創建表變量.我知道它可以是這樣的:

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?

這篇關于在 SQL Server 存儲過程中動態創建和選擇表變量?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！