<< SQL Server 2021 Hosting - HostForLIFE :: Remove CONVERTS/CASTS From WHERE/JOIN Clauses | SQL Server 2021 Hosting - HostForLIFE :: Using OPENJSON Function In SQL Server >>

SQL Server Hosting - HostForLIFE :: Table As Input Parameters For Stored Procedure

April 4, 2022 09:13 by author

Peter

This article was initially written in 2021, we try to make it done now. The content is mainly based on the MS article Table-Valued Parameters with some understanding and explanation, and with some examples to demo the results.

Introduction
Table-valued parameters were introduced to SQL Server in 2008. Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

This is the structure of this article,
    Introduction
    A - Passing Multiple Rows in Previous Versions of SQL Server
    B - What Table-Parameters is
    C - Passing a user-defined table type to a Stored Procedure in SQL Server
    D - Passing a user-defined table type to a Stored Procedure from C# Code

A - Passing Multiple Rows in Previous Versions of SQL Server
Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. A developer could choose from the following options for passing multiple rows to the server:

    Use a series of individual parameters to represent the values in multiple columns and rows of data.
    Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement.
    Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter.
    Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table.

The disadvantages of all methods above at least include one that the server side processing is necessary for them.

B - What Table-Parameters is
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

There are several limitations to table-valued 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 use ALTER TABLE statements to modify the design of table-valued parameters.

C - Passing a user-defined table type to a Stored Procedure in SQL Server
1. Creating Table-Valued Parameter Types
Table-valued parameters are based on strongly typed table structures that are defined by using Transact-SQL CREATE TYPE statements. You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. We use database Northwind.
Use Northwind

CREATE TYPE dbo.CategoryTableType AS TABLE
( CategoryID int, CategoryName nvarchar(50) )

In the Microsoft SQL Server Management Studio, we can see the created type:
Database->Programmability->Types->User Define Table Types:

2. Creating Stored Procedures in SQL Server using the Table-valued Parameters Type
Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement.

Update
CREATE PROCEDURE usp_UpdateCategories
    (@tvpEditedCategories dbo.CategoryTableType READONLY)
AS
BEGIN
    SET NOCOUNT ON
    UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;
END

Note: that the READONLY keyword is required for declaring a table-valued parameter.

3. Run the Stored Procedure with Table-Valued Parameters (Transact-SQL)
Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement.

Table Categories --- Before:

Run the Stored Procedure with Table-Valued Parameters:

DECLARE @tvpUpdateCategories AS dbo.CategoryTableType
INSERT INTO @tvpUpdateCategories([CategoryID], [CategoryName]) VALUES(8,'SeaFood1')
EXEC dbo.usp_UpdateCategories @tvpUpdateCategories

Table Categories --- After

D - Passing a Table-Valued Parameter to a Stored Procedure from C# Code
We will skip this part, you may see the detailed implementation from the bottom on Table-Valued Parameters.

Note [ref]:
Normally we provide DbType of SqlParameter for a normal parameter like varchar, nvarchar, int, and so on as in the following code.
SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@StudentName";
sqlParam.DbType = DbType.String;
sqlParam.Value = StudentName;

But in the case of a Table parameter, we do not need to provide a DbType as the parameter data type. We need to provide SqlType rather than DbType, such as
SqlParameter Parameter = new SqlParameter;
Parameter.ParameterName = "@PhoneBook";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = PhoneTable;