Home > SQL Server > Table-Valued Parameters

Table-Valued Parameters


Microsoft, starting with SQL Server 2008, added support for a new parameter type in SQL Server: Table-Valued parameters. Using them, you can send multiple rows of data to a T-SQL stored procedure, without creating a temporary table for instance.

Starting with the build 50204.579, the SQL Server producer now provides built-in support for those types. Thanks to this new feature you can now design CFQL queries such as:

<cf:method name="LoadCategories" body="load(int[] ints) where Id in (@ints)"/>

Which will generate the following code:

CREATE TYPE [dbo].[cf_type_Category_LoadCategories_0] AS TABLE(
[Item] [INT] NULL)
GO
CREATE PROCEDURE [dbo].[Category_LoadCategories]
(
@ints [dbo].[cf_type_Category_LoadCategories_0] READONLY,
@_orderBy0 [nvarchar] (64) = NULL,
@_orderByDirection0 [BIT] = 0
)
AS
SET NOCOUNT ON
DECLARE @_c_ints INT; SELECT @_c_ints= COUNT(*) FROM @ints
SELECT DISTINCT [Category].[Category_Id], [Category].[Category_Name], [Category].[_rowVersion]
FROM [Category]
WHERE [Category].[Category_Id] IN ((SELECT * FROM @ints))
RETURN
GO

The feature is actually broader than that, you can also:

  • In CFQL, use the ‘=’ operator on table-valued types, e.g. count(int[] ints) where @ints = Id,
  • Use .NET CLR types, e.g. raw(System.Uri[] uris),
  • Use already existing table-valued types in database,
  • Map table-valued types to .NET types such as DataTable, IDataReader and IEnumerable<SqlDataRecord>,
  • Map them to generated collection types you created in the model.

Want to know more about it? It’s all detailed in our product documentation, in the Array Parameters article.

Categories: SQL Server Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s