SQL Server : Data sharing across stored procedures

Table data sharing across stored procedures

This article demonstrates how table data can be shared in two stored procedures. There are several requirements where we need to pass temp table data to called stored procedure for further processing and can return manipulated data to parent stored procedure.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Introduction

I want to evaluate some of the good ways to accomplish this. I shall test approach for concurrent calling.
Table Valued Function – This can be good approach. But limitation is you can’t call stored procedure inside function if you need to.
Using Temp Table - This approach looks promising. It works for in and out.
Passing table variable – By any chance if you are using insert into in both parent and child stored procedures. Then it fails.
Other approaches like Using Cursor Variables, CLR, Open query or XML are complex, non efficient or having other pitfalls.

Evaluation of data sharing using temp table

Lets evaluate most promising approach. Test - whether temp tables are call dependent and not, causing problem in other calls.
Create table named as mytable.
CREATE TABLE [dbo].[MyTable](
            [col1] [int] NOT NULL,
            [col2] [char](5) NULL
) ON [PRIMARY]
Lets insert some sample rows for test.

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

Create child stored procedure named as called_procedure. Here first I am checking whether temp table is existing or not. If exists then insert some data as per passed parameter @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

Create caller Stored procedure without creating temp table in scope.

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

Another caller stored procedure with temp table. This procedure can be called for different parameters.

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

Execute all following queries same time. You can also execute these procedures same time from different systems.

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 takes it’s own temp table and drops when scope ends. It does not take temp table which we have created outside the proc.

Summary

This little interesting exercise can help you to understand how you can pass temp table to child stored procedures. Using this concept you can write multipurpose stored procedures to increase reusability of code.

If this article helps you in designing/coding SQL logic don’t forget to hit voting option. Please comment your suggestions and improvements.

Happy Coding!!

Search Tags:

SQL, DML, DDL, SQL SERVER 2008, SQL SERVER 2005, SQL SERVER 2000, SP, Stored Procedure, Function, Temp Table, Table Variable, Passing temp table to Stored Procedure, Table data sharing across stored procedures, Data sharing in SQL, Writing reusable Stored Procedures, Passing Rows to Stored Procedures