SQL Tips

From the below interesting article How to Share Data Between Stored Procedures. I want to try for

• TVP (Table-Valued Parameters) in SQL 2008
• Temp Tables
• Global Temp Tables 

Table-Valued Parameters in SQL 2008 - Helps address the need to pass “array” of elements to stored procedure / function

Example

STEP 1
DROP TABLE test;
DROP PROCEDURE INSERTTEST;
DROP TYPE TT;

STEP 2
CREATE TABLE TEST (Id int not null, Name varchar(100))
GO
CREATE TYPE TT AS TABLE (Id int not null, Name varchar(100))
GO

STEP 3, READ ONLY OPTION MANDATORY
CREATE PROCEDURE INSERTTEST (@TableVariable TT READONLY)
AS
BEGIN
INSERT INTO TEST
SELECT * FROM @TableVariable
END
GO

STEP 4, ROW CONSTRUCTORS
DECLARE @TTTABLE AS TT
INSERT INTO @TTTABLE VALUES
(1,'Sijo'),
(2,'Sivaram'),
(3,'Adam')
EXEC INSERTTEST @TableVariable = @TTTABLE

STEP 5
SELECT * FROM TEST
Hope this example explains the use of TVP...

Next is use of Temp Tables for passing data
STEP 1
use tempdb
IF OBJECT_ID ('OuterProc1') IS NOT NULL DROP PROCEDURE OuterProc1
IF OBJECT_ID ('InnerProc2') IS NOT NULL DROP PROCEDURE InnerProc2

STEP 2
CREATE PROCEDURE dbo.OuterProc1
(@A INT,
@B INT,
@C INT OUTPUT
)
AS
BEGIN
    IF OBJECT_ID('tempdb..#TEMPTable') is not null
        DROP TABLE #TEMPTable
    CREATE TABLE #TEMPTable (A INT, B INT)
    INSERT INTO #TEMPTable(A,B) VALUES(@A,@B)
    EXEC InnerProc2 @C OUTPUT
    SELECT @C
END

STEP 3
CREATE PROCEDURE dbo.InnerProc2
(@C INT OUTPUT)
AS
BEGIN
    DECLARE @A INT, @B INT
    SELECT @A= A, @B = B
    FROM #TEMPTable
    SELECT @C = @A+@B
END

STEP 4
DECLARE @A INT, @B INT, @C INT
SELECT @A=10, @B=15
EXEC dbo.OuterProc1 @A, @B, @C OUTPUT
SELECT @C

Global Temp Tables - They have ## in their Declaration and usage
STEP 1
use tempdb
IF OBJECT_ID ('FirstProc1') IS NOT NULL DROP PROCEDURE FirstProc1
IF OBJECT_ID ('SecondProc2') IS NOT NULL DROP PROCEDURE SecondProc2

STEP 2
CREATE PROCEDURE dbo.FirstProc1
(@A INT,
@B INT
)
AS
BEGIN
    IF OBJECT_ID('tempdb..##TEMPTable') is not null
        DROP TABLE ##TEMPTable
    CREATE TABLE ##TEMPTable (A INT, B INT)
    INSERT INTO ##TEMPTable(A,B) VALUES(@A,@B)
END

STEP 3
CREATE PROCEDURE SecondProc2
AS
BEGIN
    UPDATE ##TEMPTable
    SET A = 1000
    SELECT * FROM ##TEMPTable
END

STEP 4
DECLARE @A INT, @B INT
SELECT @A=10, @B=15
EXEC dbo.FirstProc1 @A, @B
EXEC dbo.SecondProc2