在存储过程之间共享数据 [英] Sharing data between stored procedures

问题描述

我有一个名为 dbo.Match 的存储过程.它看起来像这样:

I have a stored procedure called dbo.Match. It looks like this :

CREATE Procedure [dbo].[MATCH]
@parameterFromUser  nvarchar(30),
@checkbool int
As 
Begin
--SOME CODE
select RowId, 
       PercentMatch 
from @Matches
End

这个过程正在被另一个存储过程调用:

This procedure is being called from another stored procedure :

CREATE Procedure MatchMotherFirstName 
@MotherFN nvarchar(20) , @checkbool int
As begin
 SELECT @constVal = FunctionWeight 
 FROM   dbo.FunctionWeights 
 WHERE  FunctionWeights.FunctionId = 20;
 /*
    Some code to execute `dbo.Match` procedure in above procedure called `MatchMotherFirstName` , retrieve `RowNumber` and `PercentMatch`,
    Insert into #Temp in their respective fields , and calculate `PercentMatch * constVal`,
    and insert in corresponding column called `percentage` in `#Temp`
 */
End 

我需要在上面的过程中执行 dbo.Match 存储过程，检索 RowID 和 PecrntMatch 值，@constval 值我们上面，乘以 @constVal 和 percentmatch 并将其存储在 #Temp 的 Percentage 列中并插入来自 dbo 的结果.Match 过程 在一个临时表中.dbo.Match 仅返回 RowId 和 PercentMatch.

I need to execute dbo.Match stored procedure in above procedure, retrieve RowID and PecrntMatch value, @constval value we have above, multiply @constVal and percentmatch and store it in Percentage column of #Temp and insert results from dbo.Match procedure in a temporary table. dbo.Match returns only RowId and PercentMatch.

临时表的结构:

create table #Temp
(
Rownumber int not null,
ValFromUser nvarchar(30),
ColumnName nvarchar(30),
ValFromFunc decimal(18, 4),
FuncWeight decimal(18, 4),    -- @constVal here
Percentage decimal(18, 4) not null, -- calculated value here i.e (FuncWeight * ValFromFunc)

);

在#Temp中，我需要插入@constVal的值并计算一列并插入即PercentMatch * contVal for仅在此执行调用中插入的行.如何以最有效的方式在上述过程中执行此操作?

In #Temp, I need to insert the value of @constVal as well as calculate a column and insert i.e PercentMatch * contVal for rows inserted in this execution call only. How can I do this in above procedure in most efficient way ?

为了清楚起见，如果 dbo.Match 是一个函数而不是一个过程，我会这样做:

Edit : For purpose of clarity , here is what I was doing if dbo.Match was a function and not a procedure:

  if @MotherFN is not null 
  begin

    SELECT @constVal = FunctionWeight 
    FROM   dbo.FunctionWeights 
    WHERE  FunctionWeights.FunctionId = 20;
    INSERT INTO #Temp2                   
(RowNumber,ValFromUser,ColumnName,ValFromFunc,FuncWeight,percentage)
  SELECT RowId, 
         @MotherFN  ,
        'mothersfirstname'
        ,PercentMatch,
         @constVal,
         PercentMatch * @constVal
  FROM   dbo.MatchMatch(@MotherFN, 0)

    end

就像我可以检索 Percentmatch、@constval 的值并将它们相乘以插入 #Temp 一样，我如何在执行 dbo 时执行此操作.Match 过程而不是调用 dbo.Match 函数?

Like I can retrieve value of Percentmatch, @constval and multiply them both to insert in #Temp , how I may do this while I execute the dbo.Match procedure instead of calling dbo.Match function ?

推荐答案

您有多种选择，从非常简单到过于复杂.执行您所描述的操作的最简单(也是最有效)的方法是:

You have several options, ranging from incredibly easy to overly complicated. The easiest (and most efficient) ways of doing what you describe are:

1. 不要这样做:只需在查询中包含该计算.为什么需要在表定义中?

1. Don't do it: just include that calculation in the query. Why does it need to be in the table definition?

在创建临时表时添加一个计算列.这要求您还包括一个用于存储常量值"的字段.以便它可以被计算列引用.如果计算有点昂贵和/或会有很多行并且经常从中选择(并且可能在 WHERE 和/或 ORDER BY 子句中使用)，那么您可以使计算列 PERSISTED 以便它是根据 INSERT 和任何更新计算列中引用的字段的 UPDATE 计算的.

Add a computed column the temp table when it is created. This requires that you also include a field to store the "Constant Value" so that it can be referenced by the computed column. If the calculation is somewhat expensive and/or there will be lots of rows and frequently selected from (and possibly used in WHERE and/or ORDER BY clauses), then you can make the computed column PERSISTED so that it is calculated upon INSERT and any UPDATE that updates the fields referenced in the computed column.

在创建临时表后，在临时表中添加一个计算列.这允许嵌入恒定值"进入计算列，这样就不需要 [ConstantValue] 列.如果计算有点昂贵和/或会有很多行并且经常从中选择(并且可能在 WHERE 和/或 ORDER BY 子句中使用)，那么您可以使计算列 PERSISTED 以便它是根据 INSERT 和任何更新计算列中引用的字段的 UPDATE 计算的.

Add a computed column the temp table after the table has been created. This allows for embedding the "Constant Value" into the computed column so that there is no need for a [ConstantValue] column. If the calculation is somewhat expensive and/or there will be lots of rows and frequently selected from (and possibly used in WHERE and/or ORDER BY clauses), then you can make the computed column PERSISTED so that it is calculated upon INSERT and any UPDATE that updates the fields referenced in the computed column.

附言以防万一您发现自己问为什么不只在一步而不是两步中动态创建临时表?":在动态 SQL 中创建的本地临时表将在 EXEC 后不复存在那个动态 SQL.全局临时表将在执行动态 SQL 后继续存在，但随后表名在所有会话之间共享，因此同时执行此代码的另一个会话会在名称冲突时出错.在这种情况下，您需要通过 NEWID() 生成一个 GUID 以用作全局临时表名称并将该值连接到动态 SQL 中，但随后您就被要求使用动态SQL 用于对全局临时表的所有引用(包括用于 INSERT...EXEC)，这只是更多的工作，没有任何好处.

P.S. Just in case you find yourself asking "why not just create the temp table dynamically in one step instead of two steps?": a local temporary table created in Dynamic SQL will cease to exist after the EXEC of that Dynamic SQL. A global temp table will survive the execution of the Dynamic SQL, but then the table name is shared across all sessions so another session executing this code at the same time would error on the name conflict. In that case you would need to generate a GUID via NEWID() to use as the global temp table name and concatenate that value as well into the Dynamic SQL, but then you are stuck being required to use Dynamic SQL for all references to the global temp table (including for the INSERT...EXEC) and that is just more work for no benefit.

测试设置

IF (OBJECT_ID(N'tempdb..#InnerProc') IS NOT NULL)
BEGIN
  DROP PROCEDURE #InnerProc;
END;
GO

IF (OBJECT_ID(N'tempdb..#TempResults1') IS NOT NULL)
BEGIN
  DROP TABLE #TempResults1;
END;
IF (OBJECT_ID(N'tempdb..#TempResults2') IS NOT NULL)
BEGIN
  DROP TABLE #TempResults2;
END;
IF (OBJECT_ID(N'tempdb..#TempResults3') IS NOT NULL)
BEGIN
  DROP TABLE #TempResults3;
END;
GO

CREATE PROCEDURE #InnerProc
AS
SET NOCOUNT ON;

  SELECT TOP 20 so.[object_id], so.[modify_date]
  FROM   [master].[sys].[objects] so
  ORDER BY so.[modify_date] DESC;
GO

选项 1

CREATE TABLE #TempResults1
(
  [ObjectId] INT NOT NULL,
  [ModifyDate] DATETIME NOT NULL
);

DECLARE @ConstantValue1 INT;
SET @ConstantValue1 = 13;

INSERT INTO #TempResults1 ([ObjectId], [ModifyDate])
  EXEC #InnerProc;

SELECT 1 AS [Test], *, DATEADD(DAY, @ConstantValue1, [ModifyDate]) AS [SomeCalculation]
FROM #TempResults1;

选项 2

CREATE TABLE #TempResults2
(
  [ObjectId] INT NOT NULL,
  [ModifyDate] DATETIME NOT NULL,
  [ConstantValue] INT NULL, -- will be added via UPDATE
  [SomeCalculation] AS (DATEADD(DAY, [ConstantValue], [ModifyDate])) -- PERSISTED ??
);

INSERT INTO #TempResults2 ([ObjectId], [ModifyDate])
  EXEC #InnerProc;

SELECT 2 AS [Test], * FROM #TempResults2;

UPDATE #TempResults2
SET    [ConstantValue] = 13;

SELECT 2 AS [Test], * FROM #TempResults2;

选项 3

DECLARE @ConstantValue3 INT;
SET @ConstantValue3 = 13;

CREATE TABLE #TempResults3
(
  [ObjectId] INT NOT NULL,
  [ModifyDate] DATETIME NOT NULL
);

INSERT INTO #TempResults3 ([ObjectId], [ModifyDate])
  EXEC #InnerProc;

SELECT 3 AS [Test], * FROM #TempResults3;

-- The next 3 lines could be done just after the CREATE TABLE and before the INSERT,
-- but doing it now allows for seeing the "before" and "after" with the data.
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'ALTER TABLE #TempResults3 ADD [SomeCalculation] AS (DATEADD(DAY, '
           + CONVERT(NVARCHAR(10), @ConstantValue3) + N', [ModifyDate])); --PERSISTED';
EXEC (@SQL);

SELECT 3 AS [Test], * FROM #TempResults3;

这篇关于在存储过程之间共享数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！