在 SQL Server 中从存储过程选择数据

sachin bankar

4.11/5 (5投票s)

2012年11月7日

CPOL

4分钟阅读

106016

如何在 Select 语句中使用存储过程的结果集。

什么是从存储过程 SELECT？

在编写 T-SQL 脚本时，您可能多次将 Select 查询的数据存储到临时表中，就像下面的查询一样。

SELECT * INTO TempUserTables FROM SYS.OBJECTS WHERE TYPE_DESC = 'USER_TABLE'

这里，出于示例目的，我使用了 SYS.OBJECTS 表

但是，如果您尝试对存储过程执行相同的操作，SQL Server 将会报错。为了有个概念，请看下面的示例

SELECT * INTO LoginData FROM (Exec SP_WHO)

如果您尝试执行上述查询，将会收到此错误

‘Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'Exec'.

现在，如果 SQL Server 不允许这种操作，那么如何在 Select 语句中使用存储过程的结果集？当然，我们可以使用 INSERT –EXEC 方法（即 INSERT INTO YourTempTable (Col1, Col2) EXEC yourSP）。但是，每个方法都有其优缺点。INSERT-EXEC 方法看起来实现起来更容易，但如果 SP 的输出发生变化，您将需要更改表的结构。这种僵化的编码是不可接受的。我们在项目中遇到了同样的问题。

我为什么上传这段代码片段？

我们的应用程序处理银行不同类型的开户表单。在 EOD（每日结束）时，我们会为每种表单类型生成单独的 Excel 报告。为了获取已处理表单的数据，我们创建了存储过程（即，为每种表单类型创建单个存储过程）。我们的客户要求一个包含所有表单类型的合并 Excel 报告。我们考虑了不同的解决方案。一些人建议创建一个全新的存储过程，另一些人建议创建一个主 SP，它将调用其他 SP（我们已经为每种表单类型创建了）并提供合并的输出。在实现主 SP 解决方案时，我们遇到了上述问题。经过大量的研发工作，我们决定使用 CLR 存储过程技术来实现一个主 SP。

CLR 存储过程基础

CLR 是 Common Language Runtime 的缩写。在 SQL Server 2005 及其更高版本中，您可以使用任何 .NET Framework 语言（包括 Microsoft Visual Basic .NET 和 Microsoft Visual C#）编写存储过程、触发器、用户定义类型、用户定义函数、用户定义聚合函数和流式表值函数。在许多情况下，CLR 比 T-SQL 更快。CLR 主要用于 T-SQL 无法完成的任务。要获得 CLR 存储过程的基本知识，请遵循以下链接

创建 CLR 存储过程

假设您已经阅读了上述链接（即，如果您是 CLR 存储过程领域的初学者），我将在下面解释代码

在您的开发数据库中创建一个名为 UserLoginData 的表（这将保存存储过程的选定数据，在本例中是 sp_who）
创建另一个名为 getConnectionString 的表（这将存储一个常规连接字符串，用于 SQLBulkCopy 实例）
创建一个名为 USP_Select_Into_Table_FromSP 的 CLR 存储过程。
插入以下代码片段。这里我使用了 "CONTEXT CONNECTION=TRUE"。因为上下文连接是仅进程内连接，它可以“直接”连接到服务器，绕过网络协议和传输层发送 Transact-SQL 语句并接收结果。有关上下文连接的更多详细信息，请参阅上下文连接和常规连接的详细信息。

DataTable objSourceDataTable = new DataTable();
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
string strConnectionString = null;
SqlDataReader objDataReader = null;

using (SqlConnection objConn = new SqlConnection("CONTEXT CONNECTION=TRUE"))
{
	objConn.Open();

	SqlCommand objSelectCommand = new SqlCommand();
	objSelectCommand.CommandType = CommandType.StoredProcedure;
    objSelectCommand.CommandText = 'sp_who';  
    objSelectCommand.Connection = objConn;
	objDataAdapter.SelectCommand = objSelectCommand;
	objDataAdapter.AcceptChangesDuringFill = false; 
	objDataAdapter.Fill(objSourceDataTable);
    try
    {
        SqlCommand objCmdGetConn = new SqlCommand();
        objCmdGetConn.CommandType = CommandType.Text;
        objCmdGetConn.Connection = objConn;
        objCmdGetConn.CommandText = "select ConnectionString  from    getConnectionString";
        objDataReader = objCmdGetConn.ExecuteReader();
    }

    catch (Exception ex1)
    {
        SqlContext.Pipe.Send(ex1.Message);
    }

    while (objDataReader.Read())
    {
    strConnectionString =  Convert.ToString(objDataReader["ConnectionString"]);
     strConnectionString = strConnectionString.Trim();
    }
    objConn.Close();  //Cotext Connection Closed
}//Context Connection Ends Here

由于 SQLBulkCopy 不能与上下文连接一起使用（有关更多详细信息，请参阅上下文连接的限制），我们将为此操作使用常规连接。为此，我们将使用以下代码从数据库中获取常规连接字符串。
在上一步中，我提到 SQLBulkCopy 不能与上下文连接一起使用，但 SQLBulkCopy 是什么意思？SQLBulkCopy 是将数据从一个表或源批量加载到您的表中的一种高效方式。MSDN 在链接 SQL Bulk Copy 上提供了 SQLBulkCopy 的说明性材料。这里我使用了 SQLBulkCopy 来提高性能。
现在您拥有了常规连接字符串。使用此连接字符串，初始化 SQLBulkCopy 对象，然后将数据从 DataTable 复制到您的表中，即，在本例中是 UserLoginData。代码片段如下

//Open Bulk Copy Session with regular Connectionstring 
using (SqlBulkCopy objBulkCopy = new SqlBulkCopy(strConnectionString))
{
    objBulkCopy.DestinationTableName = "UserLoginData";
    //objBulkCopy.ColumnMappings.Add(Source Column   Name,Destination Column Name)
    objBulkCopy.ColumnMappings.Add("SPID", "ULD_SPID");
    objBulkCopy.ColumnMappings.Add("Status", "ULD_Status");
    objBulkCopy.ColumnMappings.Add("Loginame", "ULD_Loginame");
    objBulkCopy.ColumnMappings.Add("Hostname", "ULD_Hostname");
    objBulkCopy.BatchSize = objSourceDataTable.Rows.Count;
    try
    {
        objBulkCopy.WriteToServer(objSourceDataTable);
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send(ex.Message);
    }
    finally
    {
        objBulkCopy.Close();  //Close Bulk Copy Session
    }
}

您已完成。只需释放一些应用程序内存。使用以下方式执行此操作

objDataAdapter.Dispose();
objSourceDataTable.Dispose();

在编译之前，需要在权限级别进行一些更改。转到项目属性 -> 数据库 -> 权限级别。将权限级别设置为外部（External）。如果未设置，您将收到一个异常，如下所示

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, 
  System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.".

出于调试目的，您可以在 TestScript 中编写如下脚本

exec USP_Select_Into_Table_FromSP
Select * from UserLoginData

现在构建您的解决方案并进行部署。我们将在您的开发数据库中创建一个名为 'USP_Select_Into_Table_FromSP' 的存储过程。

在 SQL Server 中从存储过程选择数据

什么是从存储过程 SELECT？

我为什么上传这段代码片段？

CLR 存储过程基础

创建 CLR 存储过程

参考文献