检索存储过程结果集的列定义-Java 学习之路

我'm working with stored procedures in SQL Server 2008 and I'已经知道我必须 INSERT INTO 已经预定义的临时表以便处理数据 . 那个's fine, except how do I figure out how to define my temp table, if I' m不是编写存储过程而不是列出其定义并通过代码读取的那个？

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

5 回答

6
所以假设你在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 的手动服务器：
```
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）：
```
SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');
```
如果我们可以执行 SELECT * ，我们也可以执行 SELECT * INTO ：
```
SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');
```
一旦#tmp表存在，我们可以通过说（假设SQL Server 2005或更高版本）来确定元数据：
```
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实例来验证等效查询 . ）

结果：
```
name      type    max_length precision scale
--------- ------- ---------- --------- -----
foo       int              4        10     0
bar       varchar          5         0     0
```
在Denali，这将更加容易得多 . 同样，第一个结果集仍然存在限制，但您不必设置链接服务器并跳过所有这些环节 . 你可以说：
```
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中也是如此，所以不要觉得新的元数据发现功能是最终的结果，它们只是让上面的内容变得不那么乏味了 .

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

http://www.sommarskog.se/share_data.html#OPENQUERY
回复于 2024-05-03T22:59:22+08:00
7

一种不太复杂的方法（在某些情况下可能就足够了）：在最终SELECT之后和FROM子句之前编辑原始SP，添加INSERT INTO tmpTable以将SP结果保存在tmpTable中 .

运行修改后的SP，最好使用有意义的参数，以获得实际数据 . 恢复该过程的原始代码 .

现在，您可以从SQL Server management studio获取tmpTable脚本或查询sys.columns以获取字段描述 .

回复于 2024-05-03T22:59:22+08:00

这是我写的一些代码 . 这个想法（正如其他人所说的）是获取SP代码，修改它并执行它 . 但是，我的代码不会更改原始SP .

第一步，获取SP的定义，剥离“创建”部分，并在声明参数后删除“AS”（如果存在） .

Declare @SPName varchar(250)
Set nocount on

Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '', @TableName varchar(36) = 'A' + REPLACE(CONVERT(varchar(36), NewID()), '-', '')

Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = 'ADMIN_Sync_CompareDataForSync'

if @@ROWCOUNT > 0
    BEGIN
        Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', 'Declare') 
        from INFORMATION_SCHEMA.ROUTINES 
        where ROUTINE_NAME = @SPName

        Select @LastParameterName = PARAMETER_NAME + ' ' + DATA_TYPE + 
            CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN '(' + 
                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END 
        from #Temp 
        WHERE ORDINAL_POSITION = 
            (Select MAX(ORDINAL_POSITION) 
            From #Temp)

        Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '  ', ' '), 1) + LEN(@LastParameterName)
    END
else
    Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', '') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName

DROP TABLE #Temp

Select @StartPos = CHARINDEX('AS', UPPER(@SQL), @StartPos)

Select @SQL = STUFF(@SQL, @StartPos, 2, '')

（注意基于唯一标识符创建新表名）现在找到代码中的最后一个'From'字，假设这是执行返回结果集的select的代码 .

Select @SQLReverse = REVERSE(@SQL)

Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)

更改代码以将结果集选择到表中（基于uniqueidentifier的表）

Select @StartPos = LEN(@SQL) - @StartPos - 2

Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ')

EXEC (@SQL)

结果集现在在一个表中，表是否为空无关紧要！

让我们得到表的结构

Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

你现在可以用这个做你的魔术

别忘了丢掉那张独特的 table

Select @SQL = 'drop table ' + @TableName

Exec (@SQL)

希望这可以帮助！

回复于 2024-05-03T22:59:22+08:00

-2
看起来在SQL 2012中有一个新的SP来帮助解决这个问题 .
```
exec sp_describe_first_result_set N'PROC_NAME'
```
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql
回复于 2024-05-03T22:59:22+08:00
52
如果你在一个受限制权限的环境中工作，其中回环链接服务器之类的东西似乎是黑魔法并且肯定是“没办法！”，但你对模式有一些权利，只有几个存储过程要处理，这是非常简单的解 .

您可以使用非常有用的 SELECT INTO 语法，该语法将创建一个包含查询结果集的新表 .

假设您的过程包含以下选择查询：
```
SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
```
而是将其替换为：
```
SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
```
当您执行它时，它将创建一个新表MyOutputTable，其中包含查询返回的结果 .

您只需右键单击其名称即可获得表定义 .

就这样！

SELECT INTO只需要能够创建新表和也适用于临时表（SELECT ... INTO #MyTempTable），但检索定义可能更难 .

但是当然如果你需要检索数千SP的输出定义，那不是最快的方法:)
回复于 2024-05-03T22:59:22+08:00

检索存储过程结果集的列定义

5 回答

相关问题