THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Table-valued parameters in SQL 2005

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/10/20/table-valued-parameters-in-sql-2005/

Published Thursday, October 20, 2011 6:59 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

mjswart said:

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?

October 20, 2011 9:31 AM
 

Frank Hell said:

Holy Cow!

What would Joe Celko say to this trigger abuse? :)

I nice idea, but intransparent - I won't use in my code...

October 20, 2011 12:57 PM
 

Atul Thakor said:

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!

October 20, 2011 4:51 PM
 

Rob Farley said:

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. :)

October 20, 2011 5:49 PM
 

Alexander Kuznetsov said:

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.

October 21, 2011 11:34 AM
 

Brad Schulz said:

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

November 14, 2011 1:51 AM
 

Rob Farley said:

Almost two years ago, I wrote about a method to use Table-Valued Parameters in SQL 2005 – or basically

August 28, 2013 9:33 AM
 

Rob Farley said:

Using a hammer to push in a screw isn’t a good idea, no matter how good the hammer is. We all know that.

September 9, 2013 8:24 PM
 

Dave Green (Sydney ex- RBA) said:

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.

April 17, 2014 7:23 PM
 

Bill said:

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.

November 20, 2016 9:23 PM
 

Mr. T said:

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.

June 14, 2017 12:16 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement