Table value parameters - SQL Server

Table value parameters are the best way to pass a list or dictionary or key-value to a SQL Server stored procedure, a feature available from SQL Server 2008 onwards.

It is sometimes funny, this trend of many useful software and language features going unnoticed, when their applicability and usefulness is quite high. It is also likely that the usage or non-usage, of many such features may have influenced some programming trends or caused many simpler techniques to disappear.

One such feature is the usage of custom types, in SQL Server through the Table Valued Parameter (TvP), that makes a programmer's life simple.

A TvP is a custom type that can be defined for any database with the syntax,

Create type [dbo].[typeName] as table(
fieldName (type)(size)
)

Since arrays are not built-in to SQL Server, the customer type TvP helps in passing values to stored procedures as parameters of a SQL query with a SQLCommand or a SQLDataAdapter object.

There are many ways o passing values to a TvP type but the simplest is a DataTable object. Build the custom columns (DataColumn collection) corresponding to your TvP fields and add the values  as DataRows to the DataTable object.

So, the TvP is actually a type of table - a UserDefined TableType and hence, its name ! And so, due to less resources for some reason on the web, regarding its usage, this extremely useful data type or rather, table type is rarely used with its predecessor, the XML type more preferred.

The single most important thing when using a TvP is to remember that it is a table type and therefore, when using it in stored procs or functions, it is recommended for use with an alias failing which weird error messages may crop up totally unrelated to the type's structure or usage!

Many going the bookish way do not have much use for the TvP because it can be used only with "Insert" - this is what is the definition for usage but the context is not mentioned with the restriction leading many to believe that it must be a strange type !

It can be used within the SQL Server context with "Insert" because being a table type, you need to insert values into it first and only then you can use it! But from a program context, you can use it for any purpose - "update", "delete" or whatever.

The example below shows how to create a TvP, declare it in a stored procedure and iterate through it with the help of a cursor.

Example:

For a TvP, named 'myList'

// TvP declaration in SQL Server

use myDb;
create type myList as table(
myListValue nvarchar(max) 
)

// Stored Procedure

create procedure myProc

@myListVar myList READONLY       -- ReadOnly is a required attribute for a TvP

as
declare @listName nvarchar(max)

begin
declare @getListValues cursor
set @getListValues=cursor for

-- below, you are consuming the field defined in the TvP and getting the list value.
select c.myListValue from @myList c

open @getListValues

fetch next from @myListValue into @listName

WHILE@@fetch_status=0

-- update statement

fetch next from @myListValue into @listName

END

close @getListValues
deallocate @getListValues
end

Program code:

using System.Data;
using System.Data.SqlClient;

... more statements - class etc

DataTable dataTableObject=new DataTable("typeName");
dataTableObject.Columns.Add("fieldName");
dataTableObject.Rows.Add("SomeArrayValue");
....

The name of the DataTable, supplied as a parameter to its constructor, is the custom type defined in the database. The column collection corresponds to as many fields as is defined in the type.

SQLCommand/SQLDataAdapter cmd=new SQLCommand/SQLDataAdapter("StoredProcName",sqlConnectionObject);

cmd.CommandType=CommandType.StoredProcedure;

cmd.Parameters.Add("storedProcParameterName",SQLDbType.Structured);

cmd.Parameters["storedProcParameterName"].Value=dataTableObject;

cmd.ExecuteNonQuery(); // being an update query in the stored procedure.