Using Table-valued parameters in SQL Server
This article outlines what is Table-valued parameter; how to use table-valued parameter in Stored Procedure; How to pass table-valued parameter fro client application; and restriction on the table-valued parameter.
This article outlines what is Table-valued parameter; how to use table-valued parameter in Stored Procedure; How to pass table-valued parameter fro client application; and restriction on the table-valued parameter.
Table-valued parameters are used to send multiple rows of data from client application to SQL Server in a single
parameterized command. Table-valued parameters are based on strognly-typed table type. You need to create a table type
and define the structure in SQL Server to use table-valued parameters in your stored procedure and client applications.
Creating table type:
CREATE TYPE dbo.TestTableType AS TABLE
(Col1 int, col2 int)
Declaring table-valued parameters based on defined table type in stored procedure:
CREATE PROCEDURE TestSP
( @TestVariable dbo.TestTableType READONLY)
AS
INSERT INTO dbo.TestTable (col1,col2)
SELECT Col1, Col2 FROM @TestVariable;
Passing a table-valued parameter to a stored procedure from a client application:
SqlCommand insertCommand = new SqlCommand(
"TestSP", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@TestVariable ", TableWithCol1AndCol2);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.TestTableType";
Restrictions on table-valued parameters:
SQL Server does not maintain statistics on table-valued parameters.
Table-valued parameters must be passed as READONLY parameters to Stored procedures. DML(UPDATE,DELETE,INSERT) operations
cannot be performed on table-valued parameter in the stored procedure.
Table-valued parameter cannot be a target of a SELECT INTO or INSERT EXEC statement.