开发者

Conditional Where statement on a table valued parameter?

开发者 https://www.devze.com 2023-01-17 08:49 出处:网络
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?

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)
0

精彩评论

暂无评论...
验证码 换一张
取 消