SQL Table Valued Parameter - Default Value

Question

I'm passing a table valued parameter to a stored procedure to use in a where clause, but I want to check whether the parameter has been passed-in or not first. What is the best way to check a parameter of this type? For example, I want the select to be along the lines of:

SELECT * 
FROM tableName
WHERE
@TVP IS NULL OR RecordID IN (SELECT * FROM @TVP)

You should take a look at this article on the topic. sqlinthewild.co.za/index.php/2009/09/15/… — Sean Lange
– Sean Lange, Commented Aug 19, 2015 at 18:46
The TVP is a table, not a variable. You can't say WHERE dbo.tableName IS NULL so try WHERE NOT EXISTS (SELECT 1 FROM @TVP). Also you should name the column instead of saying *. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 19, 2015 at 18:56

Brian Pressler · Accepted Answer · 2015-08-19 19:17:28Z

3

If a parameter of a table valued type is not passed, it will just be an empty table. So you can use:

SELECT * 
FROM tableName
WHERE not exists(select * from @TVP)
    or RecordID IN (SELECT * FROM @TVP)

edited Aug 19, 2015 at 19:17

answered Aug 19, 2015 at 18:56

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Bertrand Over a year ago

Well, I wouldn't recommend bothering with a count, this requires a full scan which can be expensive if the TVP can be large.

Brian Pressler Over a year ago

Good point... I was just in the middle of editing my answer when your comment popped up. :)

Collectives™ on Stack Overflow

SQL Table Valued Parameter - Default Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related