Passing Entire table as Parameters of Stored Procedure

Introduction

In this article I am trying to demonstrate, how to pass a entire table as a parameters of stored procedure. But this facility is from MS SQL Server 2008 only. SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions.

Let's start with an example

Here I am taking a simple example to understand the process only. I have two table object and I want to copy all the data from first table to second one  by using a stored procedure

Step-1 [ Create the Base Table Objects ]

-- Source Table Objects

IF OBJECT_ID(N'dbo.Tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[Tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[Tbl_EMPLOYEE]

       (EMPID   INT         NOT NULL IDENTITY PRIMARY KEY,

        EMPNAME VARCHAR(50) NOT NULL);

GO

-- Inserting Records in my Source Table

INSERT INTO [dbo].[Tbl_EMPLOYEE]

       (EMPNAME)

VALUES ('Joydeep Das'),

       ('Chandan Bannerjee');

      

-- Now the Destination Table Object                       

IF OBJECT_ID(N'dbo.Tbl_EMPLOYEE_BACKUP', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[Tbl_EMPLOYEE_BACKUP];

   END

GO

CREATE TABLE [dbo].[Tbl_EMPLOYEE_BACKUP]

       (EMPID   INT         NOT NULL,

        EMPNAME VARCHAR(50) NOT NULL);

GO

My main target is to copy data from Tbl_EMPLOYEE to Tbl_EMPLOYEE_BACKUP

Step-2 [ Create Table Type ]

Now we are setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine.

This allows us to define the type of table and reuse it as needed in our stored procedure code.

IF EXISTS(SELECT * FROM sys.table_types WHERE name like 'type_Employee')

   BEGIN

      DROP TYPE type_Employee;

   END

GO

CREATE TYPE type_Employee AS TABLE

    (

       EMPID   INT         NOT NULL,

       EMPNAME VARCHAR(50) NOT NULL

    );  

Step-3 [ Create Stored Procedure ]

IF OBJECT_ID(N'dbo.sproc_EMPDTLS', N'P') IS NOT NULL

   BEGIN

      DROP PROCEDURE [dbo].[sproc_EMPDTLS];

   END

GO

CREATE PROCEDURE [dbo].[sproc_EMPDTLS]

       (

          @p_TableVariable type_employee READONLY

       )

AS

BEGIN

    INSERT INTO [dbo].[Tbl_EMPLOYEE_BACKUP]

           (EMPID, EMPNAME)

    SELECT EMPID, EMPNAME

    FROM   @p_TableVariable;

END            

Step-4 [ Execute Stored Procedure ]

First, we must declare a variable of type type_Employee. We don't need to define the structure of the table again because it was defined when we created the type.

BEGIN

   DECLARE @DataTable AS type_employee;

  

   INSERT INTO @DataTable

   SELECT * FROM [dbo].[Tbl_EMPLOYEE];

  

   EXEC [dbo].[sproc_EMPDTLS]

        @p_TableVariable =  @DataTable;

END

Step-5 [ Observe the Output ]

SELECT * FROM [dbo].[Tbl_EMPLOYEE];

GO

SELECT * FROM [dbo].[Tbl_EMPLOYEE_BACKUP];

GO

Hope you Like it.

Posted by: MR. JOYDEEP DAS