Sample Header Ad - 728x90

How do I check for a null or empty table-valued parameter?

18 votes
3 answers
41417 views
I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list. My problem is that I can't figure out how to check for empty or null inside the stored procedure as "IF (@tvp IS NULL)" fails on procedure creation with the message 'Must declare the scalar variable "@tvp"'. Do I have to do a SELECT COUNT(*) on the TVP and check for zero? Code excerpt: CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS IF (@tvp IS NOT NULL) -- doesn't work BEGIN -- lots of expensive processing END ELSE BEGIN -- a little bit of cheap processing END ...
Asked by Dan (545 rep)
Dec 19, 2012, 04:35 PM
Last activity: Mar 2, 2022, 01:34 AM