存储过程之间的数据表共享

背景

为了测试查询和概念，我使用的是 SQL SERVER 2008 R2。

引言

在某些情况下，我们需要将临时表数据传递给调用的存储过程以进行进一步处理，并且可以将操作后的数据返回给父存储过程。我想评估一些好的方法来实现这一点。我将测试并发调用的方法。

表值函数 – 这可能是一种好的方法。但是，限制是如果需要，则无法在函数内部调用存储过程。

使用临时表 - 这种方法看起来很有希望。它适用于输入和输出。

传递表变量 – 如果父存储过程和子存储过程都使用 insert into，则可能会失败。

其他方法，如使用游标变量、CLR、Open query 或 XML，过于复杂、效率低下或存在其他缺陷。

使用临时表共享数据的评估

让我们评估最有希望的方法。测试 - 临时表是否依赖于调用，从而导致其他调用出现问题。

创建一个名为 mytable 的表。

CREATE TABLE [dbo].[MyTable]( 
[col1] [int] NOT NULL, 
[col2] [char](5) NULL 
) ON [PRIMARY] 

让我们插入一些示例行进行测试。

INSERT INTO [MyTable]
([col1],
[col2])
VALUES (1,
A)
Go
INSERT INTO [MyTable]
([col1],
[col2])
VALUES (2,
B)
GO
INSERT INTO [MyTable]
([col1],
[col2])
VALUES (3,
C)
Go
INSERT INTO [MyTable]
([col1],
[col2])
VALUES (4,
D)
Go

创建一个名为 called_procedure 的子存储过程。在这里，我首先检查临时表是否存在。如果存在，则根据传递的参数 @par1 插入一些数据。

-- If Exist then drop and create 
IF EXISTS (SELECT * 
FROM sys.objects 
WHERE object_id = Object_id(N'[dbo].[called_procedure]') 
AND type IN ( N'P', N'PC' )) 
DROP PROCEDURE [dbo].[called_procedure] 
GO 
CREATE PROCEDURE Called_procedure    @par1 INT, 
                @par2 BIT 
AS 
BEGIN 
    IF Object_id('tempdb..#mytemp') IS NOT NULL 
    BEGIN 
        INSERT INTO #mytemp 
        SELECT * 
        FROM Mytable 
        WHERE col1 = @par1 
    END 
END 
Go

创建不创建作用域内临时表的调用存储过程。

IF EXISTS (SELECT * 
FROM sys.objects 
WHERE object_id = Object_id(N'[dbo].[caller_procedure1]') 
AND type IN ( N'P', N'PC' )) 
DROP PROCEDURE [dbo].[caller_procedure1] 
GO 
CREATE PROCEDURE Caller_procedure1 
AS 
BEGIN 
    --Testing for if temp table does not exists in scope 
    EXEC Called_procedure 
            1, 
            0 
    IF Object_id('tempdb..#mytemp') IS NOT NULL 
    BEGIN 
        SELECT * 
        FROM #mytemp 
    END 
END 
GO 

另一个带有临时表的调用存储过程。此过程可以针对不同的参数进行调用。

IF EXISTS (SELECT * 
FROM sys.objects 
WHERE object_id = Object_id(N'[dbo].[caller_procedure2]') 
AND type IN ( N'P', N'PC' )) 
DROP PROCEDURE [dbo].[caller_procedure2] 
GO 
CREATE PROCEDURE Caller_procedure2 @par1 INT 
AS 
BEGIN 
    CREATE TABLE #mytemp 
    ( 
    col1 INT NOT NULL, 
    col2 CHAR(5) NULL 
    ) 
    EXEC Called_procedure 
            @par1, 
            0 
    SELECT * 
    FROM #mytemp 
END 
go 

同时执行以下所有查询。您也可以从不同的系统同时执行这些过程。

CREATE TABLE #mytemp (col1 int NOT NULL, 
col2 char(5) NULL 
) 
Exec caller_procedure2 2 
Exec caller_procedure2 4 
Exec caller_procedure2 2 
Exec caller_procedure2 4 
drop table #mytemp 
Exec caller_procedure1   

Caller_procedure2 使用自己的临时表并在作用域结束时删除它。它不使用我们在过程外部创建的临时表。

摘要

这个小而有趣的练习可以帮助您了解如何将临时表传递给子存储过程。使用这个概念，您可以编写多用途存储过程以提高代码的可重用性。

如果本技巧对您设计/编写 SQL 逻辑有所帮助，请不要忘记点击投票选项。请发表评论并提出您的建议和改进意见。

祝您编码愉快！！