TABLE VALUE PARAMETERS(TABLE TYPE) WITH EXAMPLE

• Table-Valued Parameters is a new feature introduced in SQL SERVER 2008.

•  In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.
• scope is batch.

• Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

CREATE SAMPLE TABLE :

     

CREATE TABLE DEPARTMENT

(

      DEPARTMENTID  INT  PRIMARY KEY,

       DEPARTMENTNAME    VARCHAR(30)

)

GO

          1. CREATE A TABLE TYPE AND DEFINE THE TABLE STRUCTURE:

CREATE TYPE DEPTTYPE AS TABLE

(

        DEPTID INT, 

        DEPTNAME VARCHAR(30)

);

GO

   2.DECLARE A STORED PROCEDURE THAT HAS A PARAMETER OF TABLE TYPE:

CREATE PROCEDURE INSERTDEPARTMENT

 (@INSERTDEPT_TVP DEPTTYPE READONLY)

AS

BEGIN

              INSERT INTO DEPARTMENT(DEPARTMENTID,DEPARTMENTNAME)

              SELECT * FROM @INSERTDEPT_TVP;

END

NOTES:  

•  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
•   You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

 3. DECLARE A TABLE TYPE VARIABLE AND REFERENCE THE TABLE TYPE.

DECLARE @DEPARTMENTTVP AS DEPTTYPE;

4. USING THE INSERT STATEMENT AND OCCUPY THE VARIABLE.

INSERT INTO @DEPARTMENTTVP(DEPTID,DEPTNAME)

VALUES (1,'ACCOUNTS'),

                 (2,'PURCHASE'),

                  (3,'SOFTWARE'),

                   (4,'STORES'),

                  (5,'MAARKETING');

5. WE CAN NOW PASS THE VARIABLE TO THE PROCEDURE AND EXECUTE.

EXEC INSERTDEPARTMENT  @DEPARTMENTTVP

GO

----WE EXEC 3,4,5 STEPS AT ATIME.

check  : select * from department 

ADVANTAGE:

                          Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

EXAMPLES:

BY USING TABLE VALUE PARAMETER INSERT ,DELETE ,UPDATE:


CREATE TABLE TBL_NAME1(ID INT,NAME VARCHAR(10),GENDER BIT,ADDRESS VARCHAR(10))

CREATE TYPE TY_NAME1 AS TABLE (ID INT,NAME VARCHAR(10),GENDER BIT,ADDRESS VARCHAR(10),FLAG  INT)

GO
CREATE PROC USP_NAME1 (@TY  TY_NAME1  READONLY)
AS
BEGIN
      INSERT INTO TBL_NAME1
      SELECT ID,NAME,GENDER,ADDRESS FROM @TY WHERE FLAG=0

  DELETE FROM TBL_NAME1 WHERE ID IN (SELECT ID FROM @TY WHERE FLAG=1)

     UPDATE TBL_NAME1   SET NAME=B.NAME,GENDER=B.GENDER,                                  ADDRESS=B.ADDRESS  FROM TBL_NAME1 A INNER JOIN @TY B   ON A.ID=B.ID
    WHERE FLAG=2

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(1,'CC',0,'BNR',0)
 EXEC USP_NAME1 @TY

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(1,'CC',0,'nnnn',1)
 EXEC USP_NAME1 @TY

 DECLARE @TY TY_NAME1
 INSERT INTO  @TY  VALUES(2,'CCC',0,'BNGR',2)
 EXEC USP_NAME1 @TY

----BASED ON FLAG VALUE INSERT,DELETE,UPDATE WILL PERFORM


ONLY CHECK: SELECT * FROM TBL_NAME1


LIMITATIONS OF TABLE VALUE PARAMETERS:

• YOU CANNOT PASS TABLE-VALUED PARAMETERS TO CLR USER-DEFINED FUNCTIONS.
•   TABLE-VALUED PARAMETERS CAN ONLY BE INDEXED TO SUPPORT UNIQUE OR PRIMARY KEY CONSTRAINTS.SQL SERVER DOES NOT MAINTAIN STATISTICS ON TABLE-VALUED PARAMETERS.
•  TABLE-VALUED PARAMETERS ARE READ-ONLY IN TRANSACT-SQL CODE. YOU CANNOT UPDATE THE COLUMN VALUES IN THE ROWS OF A TABLE-VALUED PARAMETER AND YOU CANNOT INSERT OR DELETE ROWS. TO MODIFY THE DATA THAT IS PASSED TO A STORED PROCEDURE OR PARAMETERIZED STATEMENT IN TABLE-VALUED PARAMETER, YOU MUST INSERT THE DATA INTO A TEMPORARY TABLE OR INTO A TABLE VARIABLE.
•       YOU CANNOT USE ALTER TABLE STATEMENTS TO MODIFY THE DESIGN OF TABLE-VALUED PARAMETERS