SQL SERVER – 2008 – Introduction to Table-Valued Parameters 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.

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.

For Example,

Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.

Department:

CREATE TABLE Department
(
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(30)
)
GO

1. Create a TABLE TYPE and define the table structure:

Solarwinds

CREATE TYPE DeptType AS TABLE
(
DeptId INT, DeptName VARCHAR(30)
);
GO

SQL SERVER - 2008 - Introduction to Table-Valued Parameters with Example 1_DeptType
2. Declare a STORED PROCEDURE that has a parameter of table type:

CREATE PROCEDURE InsertDepartment
@InsertDept_TVP DeptType
READONLY
AS
INSERT INTO
Department(DepartmentID,DepartmentName)
SELECT * FROM @InsertDept_TVP;

GO

Important points  to remember :

–  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.

SQL SERVER - 2008 - Introduction to Table-Valued Parameters with Example 2_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


SQL SERVER - 2008 - Introduction to Table-Valued Parameters with Example 3_Execute

Let’s see if the Data are inserted in the Department Table

SQL SERVER - 2008 - Introduction to Table-Valued Parameters with Example 4_Select

Conclusion:

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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – FIX : ERROR : Could Not Connect to SQL Server – TDSSNIClient initialization failed with error 0x7e, status code 0x60
Next Post
SQL SERVER – Introduction to Filtered Index – Improve performance with Filtered Index

Related Posts

87 Comments. Leave new

  • i am getting an error like “Error Number ‘156’. Error message from database execution : Incorrect syntax near the keyword ‘select’. Must declare the table variable “@a””

    CREATE procedure [dbo].[spMergetable] @testtable [dbo].[Mergetable] READONLY
    AS
    BEGIN
    MERGE [dbo].testtable AS a
    USING [dbo].[Mergetable] AS b
    ON (a.id = b.id)
    WHEN NOT MATCHED
    THEN INSERT(id, status,created_at,updated_at)
    VALUES(b.id, b.status,b.created_at, b.updated_at)
    WHEN MATCHED
    THEN UPDATE SET a.status = b.status;
    END
    GO

    Please help

    Reply
  • HI! My doubts is can we have dynamic data type of parameter in sql Function? Like can we make function in which we can pass parameter of any data type and it would work as defined? If yes, then how?

    Reply
  • Hi!
    Is there a limit on num of rows that we can send using CustomTableType?

    Thanks!!

    Reply
  • In step 2, I think the syntax is:

    CREATE PROCEDURE dbo.InsertDepartment
    @InsertDept_TVP as DeptType READONLY,
    @echo_output as varchar(8) = Null,
    etc..

    and not

    CREATE PROCEDURE InsertDepartment READONLY
    @InsertDept_TVP DeptType

    (I think it is bad HTML, as the READONLY tag on the page seems to be floating out of place)

    Reply

Leave a Reply

Menu