Wednesday, August 7, 2013

Passing Entire table as Parameters of Stored Procedure

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 PRIMARYKEY,
        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 NOTNULL
   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 namelike '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