Click here to Skip to main content
Click here to Skip to main content

Table Data Sharing Across Stored Procedures

By , 7 Aug 2012
Rate this:
Please Sign up or sign in to vote.

Background

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

Introduction

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. 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 a good approach. But the limitation is that 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

Let's evaluate the most promising approach. Test - whether temp tables are call dependent and not, causing problem in other calls.

Create a table named as mytable.

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

Let's 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 it 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 the following queries at the same time. You can also execute these procedures at the 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 its own temp table and drops when scope ends. It does not take temp table which we have created outside the proc. 

Summary

This small 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 tip helps you in designing/coding SQL logic, don’t forget to hit the voting option. Please comment and give your suggestions and improvements.

Happy coding!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

ManojKumar19
Architect
United States United States
Manoj Kumar
 
Architect, Lead Software Engineer and Senior BI Developer | Microsoft Certified Technology Specialist(MCTS) | Exp in C#, Silverlight, WPF, WCF, WF, ASP.Net 4, MVC4, Razor View Engine and EF Code First, SSRS, SSAS, SSIS, ETL, SQL Server, Usability/User Experience, HTML5, CSS3, Javascript etc.
 
He lives with his wife Supriya and daughter Tisya in Bay Area.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 8 Aug 2012
Article Copyright 2012 by ManojKumar19
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid