I'm building a query that has a bunch of optional parameters, some of which are Table-Valued Parameters. The problem that I'm facing is how to most efficiently use the TVPs in this query?
Each TVP has the type:
TABLE( [variable] nvarchar(30))
I know that I could typically:
INNER JOIN @TVP
to filter out anything that is not in the T开发者_开发百科VP list, but what if I decide not to pass any values to the TVP in my query? Then nothing will be returned (because of the inner join)
Usually this is accomplished with a conditional where statement:
WHERE (SomeVar = @SameVar OR @SameVar IS NULL)
But, with a TVP, it can't be null (at least not that I've found)
One way I have found to accomplish this is:
OUTER APPLY
(SELECT TOP(1) * from dbo.SomeTable tbl where tbl.SomeVar in
(select * from @TVP) or not exists (select * from @TVP)
AND tbl.SomeVar = SomeVar)
Unfortunately this method is horridly slow
Alternatively, I've tried:
WHERE (tbl.SomeVar in (SELECT * FROM @TVP) or not exists (SELECT * from @TVP))
This is MUCH faster, but I still feel like it may not be ideal
Any help or thoughts would be greatly appreciated! Let me know if I can clarify anything....Thanks in advance
EDIT:
So, I've come up with this, and will probably use it unless someone has a better solution:
INNER JOIN @TVP tvp
ON (tvp.SomeVar = tbl.SomeVar or tvp.SomeVar is null)
Have you tried:
DECLARE @UseTVP int
SET @UseTVP = (SELECT COUNT(*) FROM @TVP)
SELECT TOP 1 *
FROM dbo.SomeTable tbl
LEFT JOIN @TVP tvp
ON tbl.SomeVar = tvp.SomeVar
WHERE (tvp.SomeVar IS NOT NULL
OR @UseTVP = 0)
精彩评论