sql-server – 从具有多个结果集的存储过程检索数据

给出sql Server中存储多个select语句的存储过程,是否有一种方法可以在调用过程时单独使用这些结果？

例如：

alter procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

在.NET中,如果我调用这个proc,我可以使用sqlDataReader在两个结果集之间移动,所以我可以轻松地检索所有的人和汽车.然而在sql中,当我直接执行proc时,我得到两个结果集.

如果我打电话：

insert @myTempTable
    exec dbo.GetSomething;

那么它的错误是因为列定义不匹配.如果通过某些机会Person和Car具有相同的列,则将两者连接在一起,并且@myTempTable从两个表中获取所有记录,这显然也不好.

我可以定义表示两个结果集的新定制类型,并使这些输出参数代替多个select语句,但我想知道是否有更好的方法 – 某种方式将两个结果拉到临时表中,或循环遍历结果或某事.

编辑

实际上,在更仔细地查看后,即使输出表参数也不会解决这个问题 – 它们是只读的,而且在sql 2012中仍然是这样.(Connect ticket asking for this to be added)

解决方法

在Tsql土地上,你被困住了.

这是一个我用过一次的技巧(有些可能称之为半黑客).

/*  START Tsql CODE */

/*  Stored Procedure Definition */

Use Northwind
GO


IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsByCustomerId'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspOrderDetailsByCustomerId]
END


GO

CREATE Procedure dbo.uspOrderDetailsByCustomerId
(
  @CustomerID nchar(5),@ResultSetIndicator smallint = 0
)
AS

BEGIN

    SET NOCOUNT ON



    /* ResultSet #1 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 1)
    BEGIN 
        SELECT 
            c.CustomerID,c.CompanyName /*,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country,c.Phone,c.Fax */
        FROM 
            Customers c 
            JOIN Orders o ON c.CustomerID = o.CustomerID 
        WHERE 
            c.CustomerID = @CustomerID
    END


    /* */
    /* ResultSet #2 */ 

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 2)
    BEGIN 

        SELECT o.OrderID,o.CustomerID /*,o.EmployeeID,o.OrderDate,o.requiredDate,o.ShippedDate,o.ShipVia,o.Freight,o.ShipName,o.ShipAddress,o.OrderID,o.CustomerID,o.OrderDate  */
        FROM 
            Orders o 
         WHERE 
            o.CustomerID = @CustomerID
        ORDER BY 
            o.CustomerID,o.OrderID 

    END


    /* */
    /* ResultSet #3 */

    if (@ResultSetIndicator = 0 OR @ResultSetIndicator = 3)
    BEGIN 
         SELECT od.OrderID,od.ProductID /*,od.UnitPrice,od.Quantity,od.Discount  */
         FROM 
            [Order Details] od 
         WHERE 
            exists (select null from dbo.Orders  innerOrds where innerOrds.OrderID = od.OrderID and innerOrds.CustomerID = @CustomerID )
         ORDER BY 
            od.OrderID 

    END

    SET NOCOUNT OFF


END

GO 
/* Get everything */


exec dbo.uspOrderDetailsByCustomerId 'ALFKI'




    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end


    CREATE TABLE #TempCustomer
    ( 
      [CustomerID] nchar(5),[CompanyName] nvarchar(40)
    )

INSERT INTO #TempCustomer ( [CustomerID],[CompanyName])
exec dbo.uspOrderDetailsByCustomerId 'ALFKI',1

Select * from #TempCustomer



    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end


    CREATE TABLE #TempOrders
    ( 
        OrderID int,[CustomerID] nchar(5)

    )

INSERT INTO #TempOrders ( OrderID,[CustomerID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI',2

Select * from #TempOrders






    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrderDetails
    end


    CREATE TABLE #TempOrderDetails
    ( 
        OrderID int,[ProductID] int

    )

INSERT INTO #TempOrderDetails ( OrderID,[ProductID] )
exec dbo.uspOrderDetailsByCustomerId 'ALFKI',3

Select * from #TempOrderDetails


    IF OBJECT_ID('tempdb..#TempOrderDetails') IS NOT NULL
    begin
            drop table #TempOrders
    end


    IF OBJECT_ID('tempdb..#TempOrders') IS NOT NULL
    begin
            drop table #TempOrders
    end



    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end

sql-server – 从具有多个结果集的存储过程检索数据

解决方法

猜你在找的MsSQL相关文章