SSIS – Passing Table Variable Into Stored Procedure

Introduction

Table variable is introduced in Microsoft SLQ Server 2008. In this article we are trying to pass a Entire Table within a Stored procedure. Hope it will be Informative.

The Scenario

The Scenario is simple, we are going to pass the entire Table structure with data into a Stored procedure and output of the Stored procedure is saved into a Table Objects. Is it not interesting.

How we Do That

Step – 1 [ The Control Flow of Package ]

Step – 2 [ The Variables ]

Variable Name	Data Type
v_ObjectResult	Object
v_EmpID	Int32
v_EmpName	String
v_EmpDesignation	String

Step – 3 [ The Execute SQL Task named Create TYPE ]

SQL Statement

BEGIN

      IF NOT EXISTS(SELECT *

                        FROM   sys.table_types

                        WHERE  name = 'EmpLoyeeDetails')

         BEGIN

             

                  CREATE TYPE EmpLoyeeDetails AS TABLE

                   (

                     EmpId                INT,

                     EmpName              VARCHAR(50),

                     EmpDesignation CHAR(1)

                   );

        END

END

Step – 4 [ Execute SQL named Insert Values To Table Variables AND Execute SP ]

SQL Statement

BEGIN

      DECLARE @EmpDetails AS EmpLoyeeDetails;

     INSERT INTO @EmpDetails

            (EmpId,EmpName,EmpDesignation)

        VALUES(1,'Avijit','A'),

            (2,'Joydeep','B'),

            (3,'Arabinda','B'),

            (4,'Santinath','C')

      EXEC proc_DisplyRecord

           @p_Table=@EmpDetails;

END

The Stored Procedure Sample

CREATE PROCEDURE proc_DisplyRecord

   (

     @p_Table  EmpLoyeeDetails READONLY

   )

AS

BEGIN

    SELECT * FROM @p_Table;

END

Step – 5 [ ForEach Loop Container ]

Step – 6 [ Execute SQL Task named Insert Into Table Object ]

SQL Statement

INSERT INTO tbl_EmpLoyeeDetails

   (EmpId, EmpName, EmpDesignation)

VALUES(?, ?, ?);

Step – 7 [ Execute Package and Observation ]

SELECT * FROM tbl_EmpLoyeeDetails;

Output:

EmpId   EmpName           EmpDesignation

1              Avijit                   A

2              Joydeep               B

3              Arabinda              B

4              Santinath              C

Hope you like it.

Posted by: MR. JOYDEEP DAS