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.

Leave a comment

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