Table-valued parameters in SQL 2005

October 20, 2011

Can’t be done? Oh, but it can. Let me show you.

Just quickly, a Table-Valued Parameter is a useful thing introduced in SQL 2008 that lets you have a read-only parameter which is a table type, passed into a stored procedure. To use it you need to have a user-defined table type, so that you can define what is going to be passed in. You can read about them at http://msdn.microsoft.com/en-us/library/bb510489.aspx

The 2008 code looks something like this:

/* First create a database to play in */          
create database TVPDemo;         
go         
use TVPDemo;         
go    
/* And create a table with some sample data. I’m getting mine from AdventureWorks */          
select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID         
into dbo.Products         
from AdventureWorks.Production.Product;         
go    
/* Now for the real stuff – create a table type */          
create type dbo.NewProducts as table         
(ProductName nvarchar(100) collate Latin1_General_CI_AS         
,ListPrice money         
,SubCategory int         
,DeleteMe bit         
);         
go    
/* And a stored procedure which uses this table type */          
create procedure dbo.MaintainProducts(@NewProducts dbo.NewProducts readonly) as         
begin         
/* Obviously we could MERGE – that’d work nicely here. But I want 2005 features */

/* First create a database to play in */

create database TVPDemo;

use TVPDemo;

/* And create a table with some sample data. I’m getting mine from AdventureWorks */

select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID

into dbo.Products

from AdventureWorks.Production.Product;

/* Now for the real stuff – create a table type */

create type dbo.NewProducts as table

(ProductName nvarchar(100) collate Latin1_General_CI_AS

,ListPrice money

,SubCategory int

,DeleteMe bit

);

/* And a stored procedure which uses this table type */

create procedure dbo.MaintainProducts(@NewProducts dbo.NewProducts readonly) as

begin

/* Obviously we could MERGE – that’d work nicely here. But I want 2005 features */

/* Update some products into Products table */         
update p set ListPrice = n.ListPrice, ProductSubcategoryID = n.SubCategory         
from dbo.Products p         
join @NewProducts n         
on n.ProductName = p.ProductName         
where n.DeleteMe = cast(0 as bit);    
/* Insert some */         
insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)         
select n.ProductName, n.ListPrice, n.SubCategory         
from @NewProducts n         
where not exists (select * from dbo.Products p where p.ProductName = n.ProductName)         
and n.DeleteMe = cast(0 as bit);    
/* And delete some */ 
delete p         
from dbo.Products p         
join @NewProducts n         
on n.ProductName = p.ProductName         
where n.DeleteMe = cast(1 as bit);    
/* Now list them all, returning this to the client */ 
select *         
from dbo.Products;

/* Update some products into Products table */

update p set ListPrice = n.ListPrice, ProductSubcategoryID = n.SubCategory

from dbo.Products p

join @NewProducts n

on n.ProductName = p.ProductName

where n.DeleteMe = cast(0 as bit);

/* Insert some */

insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)

select n.ProductName, n.ListPrice, n.SubCategory

from @NewProducts n

where not exists (select * from dbo.Products p where p.ProductName = n.ProductName)

and n.DeleteMe = cast(0 as bit);

/* And delete some */

delete p

from dbo.Products p

join @NewProducts n

on n.ProductName = p.ProductName

where n.DeleteMe = cast(1 as bit);

/* Now list them all, returning this to the client */

select *

from dbo.Products;

end        
go    
/* Now let’s familiarise ourselves with what’s in Product now */          
select *         
from dbo.Products;    
/* And do some maintenance on it. We create a table variable of the appropriate type, populate it and call the proc */         
declare @SomeNewProducts dbo.NewProducts;         
insert @SomeNewProducts (ProductName, ListPrice, SubCategory, DeleteMe)         
select 'Blade', 0.1, 1, 0         
union all         
select 'Blade2', 0.1, null, 0         
union all         
select 'Bearing Ball', 1, 2, 1         
;    
exec dbo.MaintainProducts @SomeNewProducts;    
/*        
When we ran this stored procedure, the latest version of dbo.Products was outputted, so we can clearly see the new record, and the absence of the one we deleted.         
Lovely         
*/

end

/* Now let’s familiarise ourselves with what’s in Product now */

select *

from dbo.Products;

/* And do some maintenance on it. We create a table variable of the appropriate type, populate it and call the proc */

declare @SomeNewProducts dbo.NewProducts;

insert @SomeNewProducts (ProductName, ListPrice, SubCategory, DeleteMe)

select 'Blade', 0.1, 1, 0

union all

select 'Blade2', 0.1, null, 0

union all

select 'Bearing Ball', 1, 2, 1

;

exec dbo.MaintainProducts @SomeNewProducts;

When we ran this stored procedure, the latest version of dbo.Products was outputted, so we can clearly see the new record, and the absence of the one we deleted.

Lovely

But this wasn’t possible in SQL 2005. We didn’t have user-defined table types, and we certainly didn’t have table-valued parameters.

Except that we could still do something very similar. This was something I’d taken for granted, but when I showed this to someone at the PASS Summit, and then someone else, I got persuaded to write a blog post on it.

If you haven’t seen this idea before, I’m sure you’ll kick yourself. It’s remarkably simple, but I think it’s quite powerful. Like I said – I’d taken it for granted.

The idea is this: make a VIEW with an INSTEAD OF trigger, using the inserted table instead of the table variable.

That INSTEAD OF trigger is essentially where your stored procedure is kept. A trigger is still a procedure, it’s just not stored in the traditional list of stored procedures. But it will act just like one.

As for the view – that can just be a placeholder. Think of it as simply defining the columns you need to handle. You don’t need a FROM clause, and you don’t even need any rows to come back. I like to put a contradiction in there so that I don’t think there’s any real values coming out.

So a trigger doesn’t take a table-valued parameter, but it can leverage the inserted and deleted tables that are available in triggers. For us, we’re just interested in the former. Have a look at the code, and you’ll see what I mean.

This code can run on SQL 2005 (well, it can also run on later versions, but that’s less important).

/* First let’s set up a new database, just like we did in SQL 2008*/          
create database TVPDemo;         
go         
use TVPDemo;         
go    
select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID        
into dbo.Products         
from AdventureWorks.Production.Product;         
go    
/* Here’s the tricky bit. Make a view. Focus on the columns. I put WHERE 0=1 in, just to make it cleaner */        
create view dbo.NewProducts as        
select         
cast(N'' as nvarchar(100)) collate SQL_Latin1_General_CP1_CI_AS as ProductName,         
cast(0 as money) as ListPrice,         
cast(0 as int) as SubCategory,         
cast(0 as bit) as DeleteMe         
where 0=1         
;         
go    
/* This trigger contains the same code as in the 2008 stored procedure.        
* But instead of having a table variable, we use the inserted table.         
*/         
create trigger dbo.MaintainProducts on dbo.NewProducts instead of insert as        
begin         
/* Update some products into Products table */ 
update p set ListPrice = i.ListPrice, ProductSubcategoryID = i.SubCategory         
from dbo.Products p         
join inserted i         
on i.ProductName = p.ProductName         
where i.DeleteMe = cast(0 as bit);    
/* Insert some */         
insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)         
select i.ProductName, i.ListPrice, i.SubCategory         
from inserted i         
where not exists (select * from dbo.Products p where p.ProductName = i.ProductName)         
and i.DeleteMe = cast(0 as bit);    
/* And delete some */ 
delete p         
from dbo.Products p         
join inserted i         
on i.ProductName = p.ProductName         
where i.DeleteMe = cast(1 as bit);    
/* Now list them all, returning this to the client */          
select *         
from dbo.Products;    
end        
go    
/* Look what’s in there now */         
select *         
from dbo.Products;    
/* Remember there’s never anything in here */         
select *         
from dbo.NewProducts;    
/* Now we simply insert into our view. As we do, the trigger runs immediately and makes the changes */          
insert dbo.NewProducts (ProductName, ListPrice, SubCategory, DeleteMe)         
select 'Blade', 0.1, 1, 0         
union all         
select 'Blade2', 0.1, null, 0         
union all         
select 'Bearing Ball', 1, 2, 1

/* First let’s set up a new database, just like we did in SQL 2008*/

create database TVPDemo;

use TVPDemo;

select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID

into dbo.Products

from AdventureWorks.Production.Product;

/* Here’s the tricky bit. Make a view. Focus on the columns. I put WHERE 0=1 in, just to make it cleaner */

create view dbo.NewProducts as

select

cast(N'' as nvarchar(100)) collate SQL_Latin1_General_CP1_CI_AS as ProductName,

cast(0 as money) as ListPrice,

cast(0 as int) as SubCategory,

cast(0 as bit) as DeleteMe

where 0=1

;

/* This trigger contains the same code as in the 2008 stored procedure.

* But instead of having a table variable, we use the inserted table.

create trigger dbo.MaintainProducts on dbo.NewProducts instead of insert as

begin

/* Update some products into Products table */

update p set ListPrice = i.ListPrice, ProductSubcategoryID = i.SubCategory

from dbo.Products p

join inserted i

on i.ProductName = p.ProductName

where i.DeleteMe = cast(0 as bit);

/* Insert some */

insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)

select i.ProductName, i.ListPrice, i.SubCategory

from inserted i

where not exists (select * from dbo.Products p where p.ProductName = i.ProductName)

and i.DeleteMe = cast(0 as bit);

/* And delete some */

delete p

from dbo.Products p

join inserted i

on i.ProductName = p.ProductName

where i.DeleteMe = cast(1 as bit);

/* Now list them all, returning this to the client */

select *

from dbo.Products;

end

/* Look what’s in there now */

select *

from dbo.Products;

/* Remember there’s never anything in here */

select *

from dbo.NewProducts;

/* Now we simply insert into our view. As we do, the trigger runs immediately and makes the changes */

insert dbo.NewProducts (ProductName, ListPrice, SubCategory, DeleteMe)

select 'Blade', 0.1, 1, 0

union all

select 'Blade2', 0.1, null, 0

union all

select 'Bearing Ball', 1, 2, 1

So there you have it – a useful TVP equivalent in versions prior to SQL 2008. I get that I’m probably writing this post about ten years too late. Sorry about that.

But if you’re not fond of the idea of having to declare and populate a table variable, then perhaps this idea is for you. This method will support any type of inserting, whether it’s row-by-row, or the results of a single SELECT statement. One day though, TVPs won’t be READONLY any more (this doesn’t seem to be the case for SQL Server 2012 unfortunately), and when that happens, you’ll want to definitely be using TVPs.

@rob_farley

This Post Has 10 Comments

mjswart October 21, 2011 Reply

Hi Rob,
The biggest value I see in TVPs is to be able to send sets of data to SQL Server from an application efficiently (Where the declaration and population of tables is taken care of).
I’m curious, do you see any scenarios where this insert-into-view method would get used or is this just a neat little trick?
Frank Hell October 21, 2011 Reply

Holy Cow!
What would Joe Celko say to this trigger abuse? 🙂
I nice idea, but intransparent – I won’t use in my code…
Atul Thakor October 21, 2011 Reply

I remember when reading :
http://www.amazon.co.uk/MCTS-Self-Paced-Training-Exam-70-433/dp/0735626391
The author gave a good example of where he used XML to replace a large number of parameters being passed into a stored procedure.
I imagine a table value parameter would be an even better option to pass a large number of parameters into a stored procedure.
Also… thinking about stored procedures… people can pass null’s as parameter values which you sometimes need to handle in the sproc.
Using the TVP you can enforce non-null values being passed in where you absolutely need them!
Rob Farley October 21, 2011 Reply

Michael – The obvious scenario is 2005, of course. But also, the fact that you don’t have to declare and populate a table variable could be advantageous for some environments.
Frank – that’s ok. It’s just putting an idea out there. I realise that code in triggers is not as obvious as code in stored procs, but I would also suggest that DBAs should be aware of all the places that code can exist.
Atul – yes, there’re lots of reasons why a TVP is a more elegant solution. 🙂
Alexander Kuznetsov October 22, 2011 Reply

Very nice. Very very nice.
Rob,
Have you benchmarked it against more traditional approaches, like Erland does in his articles?
When I had to send lots of numbers, like 50K or 100K integers, over to the server, sending an array in binary format as an image worked best for me on SQL 2005.
Brad Schulz November 14, 2011 Reply

Just caught up on some of my blog reading and finally read this entry.
Always love your out-of-the-box thinking. Very nice.
–Brad
Dave Green (Sydney ex- RBA) April 18, 2014 Reply

Not 10 years too late at all. Some large Australian banks are *still* deploying enterprise-wide applications onto SQL Server 2005.
I know, I know…
Anyhoo it’s a nice idea to use the trigger, but I probably stick with passing my varchar(max) csv string as a parameter and splitting it inside the proc via a table function.
Bill November 21, 2016 Reply

Nice. I was going to do something similar, I was going to use a view to call a web service api to update the remote data. This way lower mortals can use their table skills and access programs to transparently update cloud data.
Never thought of using it as you described here. That’s a good idea.
As for worrying about whether the "view" is interpreted as a proc by a human, then just prefix the view with vsp_, for example vsp_DoIt.
Mr. T June 15, 2017 Reply

Very nice article!
I am actually passing a DataTable from my .net application to SQL Server using table type. Now I need to make that work in 2005. Not sure how to incorporate your idea into this scenario.
Pingback: Triggers – good or bad? - LobsterPot Blogs