Table-Valued Parameters

In my previous article I am trying to demonstrate, "How to pass a table or table variable in a stored procedure via xml string".

Some of my reader, asking about Table-valued parameters, the new feature of SQL Server 2008.

In this article I am trying to demonstrate related to Table-Valued parameters in SQL server 2008.

Hope it will be helpful for all of my readers.

The table-valued parameters are the new feature of SQL Server 2008. By this we can send the multiple rows of data in a stored procedure or a function without creating any temporary table. Before SQL server 2008 it is not possible to send the table or table variable in the stored procedure or function.

For the Table-value parameters we must follow the following tasks mentioned bellow.

1.    Create the table structure

2.    Create stored procedure that has Table type parameter.

3.    Create the table type variable and reference the table type.

4.    Using the INSERT statement and occupy the variable.

5.    Passes the variable to the procedure.

The Table Definition

CREATE TABLE emp

            (

                  empID   INT         NOT NULL PRIMARY KEY,

                  empName VARCHAR(30) NULL

            )

GO

Step-1 [ Create the table structure ]

CREATE TYPE empType AS TABLE

            (

                  empID    INT,

                  empName  VARCHAR(30)

            );

GO

Step-2 [ Create stored procedure that has Table type parameter ]

CREATE PROCEDURE my_Employee

      @Iemp empType READONLY

AS

INSERT INTO emp(empID,empName)

SELECT * FROM @Iemp;

GO

Please note the table-value parameter must be passes as readonly parameters. We can only perform DML operation in the body of the routine.

Step-3 [  Create the table type variable and reference the table type ]

DECLARE @Iemp AS empType;

Step-4 [ Using the INSERT statement and occupy the variable ]

INSERT INTO @Iemp(empID, empName)

VALUES (1,'Joydeep Das'),

       (2,'Sudip Das'),

       (3,'Tuhin Shina'),

       (4,'Palash Paul')

Step-5 [ Passes the variable to the procedure ]

EXEC my_Employee @Iemp;

GO      

      

SELECT * FROM  emp

The complete code set to execute

      CREATE TABLE emp

                  (

                        empID   INT         NOT NULL PRIMARY KEY,

                        empName VARCHAR(30) NULL

                  )

    GO

      CREATE TYPE empType AS TABLE

                  (

                        empID    INT,

                        empName  VARCHAR(30)

                  )

    GO

      CREATE PROCEDURE my_Employee

              @Iemp empType READONLY

      AS

      INSERT INTO emp(empID,empName)

      SELECT * FROM @Iemp;

    GO

   

      BEGIN

            DECLARE @Iemp AS empType;

            INSERT INTO @Iemp(empID, empName)

            VALUES (1,'Joydeep Das'),

                     (2,'Sudip Das'),

                     (3,'Tuhin Shina'),

                     (4,'Palash Paul')

                  

                  

            EXEC my_Employee @Iemp

      END   

   

    GO  

      SELECT * FROM  emp   

Hope you like it.

Posted by: MR. JOYDEEP DAS