Sharing Data Between Stored Procedures

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,

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

Solution 2:

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.

Solution 3:

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.