动态地将存储过程结果导出到表中， 使用 T-SQL

• 下载 XportStoredProc - 1.23 KB

引言

本文档主要面向希望将现有存储过程的输出导出到表的 **SQL Server** 数据库用户，而无需重新编码或更改它们。在实际场景中，不可能更改旧数据库系统中所有的现有存储过程对象，因为大部分业务逻辑都写在存储过程中。

此处提供的代码主要是为了演示一种将存储过程结果导出到表的方法，该对象在开发时考虑了性能因素。目标是使用极少的 **T-SQL** 代码在数据库中创建一个新对象，该对象可以在不改变现有数据库设计和架构的情况下处理此问题，同时不使用任何其他编程语言和工具。

将存储过程导出到表通常发生在我们需要将其用于日常业务智能和报告目的时。在这样的场景下，这段代码将非常有用。在数据库中执行此操作后，它可以被前端的各种应用程序或其他后端对象使用，以便从用户选择的任何存储过程中创建表。

背景

代码使用 SQL Server 对象 OPENQUERY 来创建表。有关 OPENQUERY 功能及其选项的更多信息，请参考以下链接的在线文档 http://msdn.microsoft.com/en-us/library/aa276848(v=sql.80).aspx

http://msdn.microsoft.com/en-us/library/ms188427(v=SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms188427.aspx

有关 PATINDEX 函数的更多信息，请参考在线文档 http://msdn.microsoft.com/en-us/library/ms188395(v=SQL.90).aspx

有关 SUBSTRING 函数的更多信息，请参考在线文档 http://msdn.microsoft.com/en-us/library/ms187748(v=SQL.90).aspx

OPENQUERY、PATINDEX、SUBSTRING 和 SP_EXECUTESQL 的解释超出了本文档的范围。

使用代码

此 XportStoredProc 对象将在数据库中创建一个表，表名将传递给它（例如：StoredProcedureName_Report）。通常，会在 **SQL Server** 中创建一个新数据库，并将所有表导出到其中，该数据库流量不大，用作临时数据库。这取决于用户如何使用它。

必须启用 **ServerOption**，并且 **'DATA ACCESS'** 选项需要设置为 **TRUE** 在 **SQL Server** 上。用户应具有执行此 **ServerOption** 对象的权限。

/******  This needs to be set before you execute.   ******/
DECLARE @Server VARCHAR(50) 
SET @Server = @@SERVERNAME 
-- This ServerOption Should be Turned on to use the OPENQUERY function.
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'  

此 XportStoredProc 存储过程对象可以处理 **'n'** 个参数。以下是向此对象提供参数的几个演示示例：

示例 1 ：exec [XportStoredProc] 'StoredProcedureName 2,2' (2个整数参数)

示例 2 ：exec [XportStoredProc] 'StoredProcedureName 1,''''Test''''' (整数和 Varchar 参数)

示例 3 ：exec [XportStoredProc] 'StoredProcedureName ''''AFA'''',1,''''Afghani'''''

示例 4 ：exec [XportStoredProc] StoredProcedureName 或 exec [XportStoredProc] 'StoredProcedureName'

注意：如果将存储过程两次传递给此 XportStoredProc 对象，它将删除现有表（如果存在）并用新数据重新创建它。

此实用工具对象命名为 'XportStoredProc'，可以根据用户需要进行更改。默认情况下，架构设置为 'dbo'，如果需要设置为其他架构，请进行更改。

/****** Object:  StoredProcedure [dbo].[XportStoredProc]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[XportStoredProc]
@SRCOBJECT varchar(500) ,
@SCHEMA varchar(100)= N'dbo'
AS   

此对象所需的变量。

DECLARE @vsSQL nvarchar(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @vsServerName varchar(50) 
DECLARE @vsSPName varchar(100) 
DECLARE @vsDestDBName varchar(100) 
DECLARE @vsDestTableName varchar(100) 
DECLARE @CRLF char(2) 

输出表名后跟 (_Report) 扩展名，可以根据用户需要进行自定义。

/********************************************************************** 
 CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for exporting a Stored Procedure results into a new Table Name(StoredProcedureName+_Report).
 COMMENTS     : The Below Proc can handle any number of parameters.
        Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition)
 **********************************************************************/

SET @CRLF = char(10)

-- If @SRCOBJECT does not have Parameter then to handle it
SELECT @SRCOBJECT = @SRCOBJECT + ' '
SELECT @vsServerName = '[' + Convert(VARCHAR,SERVERPROPERTY ('ServerName') ) + ']'
SELECT @vsSPName =  Convert(VARCHAR,db_name()) + '.' + @SCHEMA + '.' + @SRCOBJECT 
SELECT @vsDestTableName = Convert(VARCHAR,db_name()) + '.' + @SCHEMA + '.' +(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report'
 

使用上述变量，字符串现在被构建并传递给变量 @SQL。

/* This will drop the table if exists and recreates with new set of data as per the parameters passed to it. Table is created with the extension ProcedureName + '_Report' */

SELECT @vsSQL = '
IF  EXISTS (SELECT * FROM sysobjects WHERE name = (N'''+(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report'+ ''') AND xtype = (N''U''))
DROP TABLE [dbo].['+(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report]'+@CRLF+'
  SELECT * INTO '  +  @vsDestTableName  +  ' FROM OPENQUERY ' + '(' + @vsServerName + ','
SELECT @vsSQL = @vsSQL + '''SET FMTONLY OFF  EXECUTE ' +  @vsSPName  + '' 
SELECT @SQL = SUBSTRING(@vsSQL,1,LEN(@vsSQL)) + ''')'

最后，sp_executeSQL 将执行它。

EXEC sp_executeSQL @SQL  

此对象的性能非常好，因为它位于数据库中，并且可以在极短的时间内完成创建表设计和结构以及数据的所需工作。

结论

上述演示步骤表明，通过极少的 **T-SQL** 代码，可以在不重新编码或重新设计现有数据库存储过程的情况下，将存储过程的输出导出到表中。

因此，可以得出结论，此 T-SQL 存储过程 (XportStoredProc) 对象通过最少的编程工作，就可以实现从用户选择的任何存储过程中提取输出的功能。

希望本文能达到其目的。欢迎任何建议或指正。

关注点

基本上，在将业务逻辑从存储过程提取到表之后，现在需要将其导出到某种呈现形式。然后，我早期文章中演示的通用自动化 **SSIS** 包将非常有助于完成呈现工作。

访问以下链接，了解如何将此 **XportStoredProc** 对象创建的表导出到所需的格式。

Dynamic_Excel.aspx

Dynamic_Html.aspx

历史

2012 年 1 月 10 日