table valued parameters

I’ve been dying to give this a try.. my new job I will definitely be doing this.
——————————————————————————–
Processing of delimited strings. This is awesome and I could have used this feature…well, always. Currently, we pass in delimited strings in the following manner:

exec sp_MySproc ‘murphy,35;galen,31;samuels,27;colton,42’
Then the stored proc needs to parse the string into a usable form – a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP).

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)
DECLARE @myPeeps PeepsType
INSERT @myPeeps SELECT ‘murphy’, 35
INSERT @myPeeps SELECT ‘galen’, 31
INSERT @myPeeps SELECT ‘samuels’, 27
INSERT @myPeeps SELECT ‘colton’, 42

exec sp_MySproc2 @myPeeps

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) …
The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.

http://angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.