Sharing data between stored procedures

Question

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 

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.

Structure of Temporary table:

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)

);

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 ?

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

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 ?

Answer 1

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

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

2. 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.

3. 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.

   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.

Test Setup

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

Option 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;

Option 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;

Option 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;

Answer 2

You have 3 Easy enough options. One has a decent sized performance hit, One requires a config update on the server and One requires a change to the match stored procedure.

Option 1 In the MatchMotherFirstName procedure declare a table for the Match results.

CREATE TABLE #tmpMatchResults (Col1 , Col2....)
Insert into #tmpMatchResults
EXEC [dbo].[MATCH]

This has a performance hit, but it works without any changes to the Match proc code or server config. If you only expect very few lines, this will work just fine

Option 2 Use OpenRowSet or OpenQuery

Select * FROM OPENROWSET(connection,'Exec database.dbo.MATCH')

This requires a config change to allow data access

Option 3 Update the MATCH Stored Procedure to push the results to a temp table

CREATE Procedure [dbo].[MATCH]
--SOME CODE
select RowId, PercentMatch from #tmpMatches

Be sure NOT to drop the temp table at the end of the proc

Then In your MatchMotherFirstName procedure, while the session is active, you can call the proc

EXEC dbo.MATCH @param

and the result set with

SELECT * FROM #tmpMatches

Some people would argue that you should clean up (drop table) the temp table at the end of the MATCH proc call. You can include a parameter in the MATCH proc to persist results or do table cleanup.

Answer 3

Well, in general terms there is no value on creating complex logics if you just need to do simple stuffs. In the scenario that you described I would tend to think the best approach would be the use of phisical table that can be accessed any time by either the dbo.Match and dbo.MatchMotherFirstName procedures. If you don't want to leave it in the database after logic execution, use the CREATE/DROP sentences to CREATE/DROP the table as per your needs.