SQL 2008's table valued parameters

One of my favorite new features available in SQL 2008 is table valued parameters. For years SQL developers have been dying for this abilty and have been throwing some miserable duct tape at the problem.

Before sql 2005 it was common to see strings being passed into stored procedures and then parsed into temporary tables. This approach suffered several major disadvantages including being limited to 8000 bytes, requiring T-SQL parsing logic and little guarantee you would receive the parameter structured exactly as you desired.

SQL 2005 not only lifted the 8000 byte limitation by introducing (n)varchar(max) but also added extensive XML support which allowed structured data to be passed in as XML and later queried as if it were relational data. While very useful I was always a little bothered by having to rely on XML to perform what was often a purely relational task. After all sometimes you're merely passing in an in determinant-length list of integers; effectively an array. Resorting to a hierarchical data technology was a little like cutting down a sapling with a chainsaw.

Table valued parameters to the rescue! In the following example I'll demonstrate the basics of this feature by creating a stored procedure that returns a list of customers that are in a given list of types.

Lets' start out with a basic structure:

create table CustomerTypes (
  Customer_Type_ID int identity(1, 1)
    constraint PK_Customer_Type primary key clustered,
  [Name] varchar(256) not null
)
go

create table Customers (
  Customer_ID int identity(1, 1) 
    constraint PK_Customers primary key clustered,
  Full_Name nvarchar(256),
  Customer_Type_ID int 
    constraint FK_CustomerTypes foreign key 
      references CustomerTypes(Customer_Type_ID)
)
go

And we'll slap some data in those respective tables:

Customer_Type_ID Name
---------------- ----------------------
1                Basic
2                Intermediate
3                Advanced
4                Professional
5                Enterprise

Customer_ID 	Full_Name	 Customer_Type_ID
-----------	--------------	 ------------------------
1      		Jimmy Jones	 1
2      		Richard Rocket	 2
3      		Samantha Smith	 3
4      		Chris Candleson	 4
5      		Greg Gimble	 5
7      		Billiam Barlette 5

Table valued parameters require a user-defined table type that they can be declared as. In our case we'll use:

create type Customer_Type_IDs as table
(
	Customer_Type_ID int primary key
)
go

With our type in place we can write a stored procedure accepting of a variable of that type as an argument. Note the parameter must be passed in as read-only.

create procedure proc_Customers_ByType_Get (
	@p_Customer_Type_IDs Customer_Type_IDs readonly
) as
begin
	select C.Customer_ID,C. Full_Name, C.Customer_Type_ID
	from Customers C
		inner join @p_Customer_Type_IDs pCT
			on C.Customer_Type_ID = pCT.Customer_Type_ID
		inner join CustomerTypes CT
			on CT.Customer_Type_ID = pCT.Customer_Type_ID
end
go

Alright, we're ready to test. All we have to do is declare a variable of our table type, fill it with some data and pass it into our stored procedure. Note that if the procedure is being called from .Net code a datatable can be used as the parameter value:

declare @CustomerIDs Customer_Type_IDs

insert into @CustomerIDs
select 4
union 
select 5

exec proc_Customers_ByType_Get @CustomerIDs

Which will return:

Customer_ID 	Full_Name	 Customer_Type_ID
-----------	--------------	 ------------------------
4      		Chris Candleson	 4
5      		Greg Gimble	 5
7      		Billiam Barlette 5

Easy as pie, eh? Clearly this feature allows database developers to replace error prone and misplaced methodologies with something clean, strongly typed and performance friendly.

Sun May 11 2008 16:05:34 GMT+0000 (UTC)