检索存储过程结果集的列定义 [英] Retrieve column definition for stored procedure result set

问题描述

我正在使用 SQL Server 2008 中的存储过程，后来我了解到我必须INSERT INTO 一个已预定义的临时表，以便处理数据.没关系，除了我不知道如何定义我的临时表之外，如果我不是编写存储过程的人，而不是列出其定义并阅读代码?

I'm working with stored procedures in SQL Server 2008 and I've come to learn that I have to INSERT INTO a temp table that has been predefined in order to work with the data. That's fine, except how do I figure out how to define my temp table, if I'm not the one that wrote the stored procedure other than listing its definition and reading through the code?

例如，对于`EXEC sp_stored_procedure'，我的临时表会是什么样子?这是一个简单的存储过程，我大概可以猜出数据类型，但似乎必须有一种方法可以读取执行过程返回的列的类型和长度.

For example, what would my temporary table look like for `EXEC sp_stored_procedure'? That is a simple stored procedure, and I could probably guess at the data types, but it seems there must be a way to just read the type and length of the columns returned from executing the procedure.

推荐答案

假设您在 tempdb 中有一个存储过程:

So let's say you have a stored procedure in tempdb:

USE tempdb;
GO

CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
    SET NOCOUNT ON;

    SELECT foo = 1, bar = 'tooth';
END
GO

有一种非常复杂的方法可以用来确定存储过程将输出的元数据.有几个警告，包括该过程只能输出一个结果集，如果不能准确确定数据类型，将对数据类型进行最佳猜测.它需要使用OPENQUERY 和一个'DATA ACCESS' 属性设置为true 的环回链接服务器.您可以检查 sys.servers 以查看您是否已经有一个有效的服务器，但让我们手动创建一个名为 loopback 的服务器:

There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can only output a single result set, and that a best guess will be made about the data type if it can't be determined precisely. It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

既然您可以将其作为链接服务器进行查询，您就可以将任何查询(包括存储过程调用)的结果用作常规 SELECT.所以你可以这样做(注意数据库前缀很重要，否则你会得到错误11529和2812):

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

如果我们可以执行一个SELECT *，我们也可以执行一个SELECT * INTO:

If we can perform a SELECT *, we can also perform a SELECT * INTO:

SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');

一旦#tmp 表存在，我们可以通过说(假设 SQL Server 2005 或更高版本)来确定元数据:

And once that #tmp table exists, we can determine the metadata by saying (assuming SQL Server 2005 or greater):

SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale
  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('tempdb..#tmp');

(如果你使用的是 SQL Server 2000，你可以用 syscolumns 做类似的事情，但我没有 2000 实例来验证等效的查询.)

(If you're using SQL Server 2000, you can do something similar with syscolumns, but I don't have a 2000 instance handy to validate an equivalent query.)

结果:

name      type    max_length precision scale
--------- ------- ---------- --------- -----
foo       int              4        10     0
bar       varchar          5         0     0

在 Denali 中，这将变得容易得多.同样，第一个结果集仍然存在限制，但您不必设置链接服务器并跳过所有这些环节.你可以说:

In Denali, this will be much, much, much easier. Again there is still a limitation of the first result set but you don't have to set up a linked server and jump through all those hoops. You can just say:

DECLARE @sql NVARCHAR(MAX) = N'EXEC tempdb.dbo.my_procedure;';

SELECT name, system_type_name
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);

结果:

name      system_type_name
--------- ----------------
foo       int             
bar       varchar(5)      

在 Denali 之前，我建议卷起袖子，自己弄清楚数据类型会更容易.不仅因为执行上述步骤很乏味，还因为您比引擎更有可能做出正确(或至少更准确)的猜测，因为引擎做出的数据类型猜测将基于运行时输出，而无需任何可能值域的外部知识.这个因素在 Denali 中也将保持不变，所以不要以为新的元数据发现功能是万能的，它们只是让上述内容变得不那么乏味.

Until Denali, I suggest it would be easier to just roll up your sleeves and figure out the data types on your own. Not just because it's tedious to go through the above steps, but also because you are far more likely to make a correct (or at least more accurate) guess than the engine will, since the data type guesses the engine makes will be based on runtime output, without any external knowledge of the domain of possible values. This factor will remain true in Denali as well, so don't get the impression that the new metadata discovery features are a be-all end-all, they just make the above a bit less tedious.

哦，对于 OPENQUERY 的其他一些潜在问题，请参阅 Erland Sommarskog 的文章:

Oh and for some other potential gotchas with OPENQUERY, see Erland Sommarskog's article here:

http://www.sommarskog.se/share_data.html#OPENQUERY

这篇关于检索存储过程结果集的列定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！