Clever Geek Handbook

Conditional AND T-SQL Operator

I am trying to set up a stored procedure where I take a table value parameter and use it inside an IN statement to capture the results, the problem is the table value parameter may be empty, is there a way for me to conditionally insert an integer and a statement?

declare @var int
--insert @var
declare @tvp tvp
--insert stuff (or not) into @tvp

SELECT t.foo1, t.foo2, t.foo3
FROM dbo.t t
WHERE t.foo4 = @var
IF(EXIST (SELECT 1 FROM @tvp)) 
   AND t.foo1 IN (SELECT @tvp.foo1 FROM @tvp)

this is what i'm going conceptually, any help on what is the correct way to do this?

+3


source to share


3 answers


This version assumes that results should be returned even if @tvp is empty. It says @tvp is either empty or t.foo1 is IN @tvp.



WHERE t.foo4 = @var AND
(  
    NOT EXISTS (SELECT 1 FROM @tvp) 
    OR t.foo1 IN (SELECT @tvp.foo1 FROM @tvp)
)
+3


source


Following your logic, you just need the parentheses:



WHERE t.foo4 = @var OR
      (NOT EXISTS (SELECT 1 FROM @tvp) OR
       t.foo1 IN (SELECT @tvp.foo1 FROM @tvp)
      )
+4


source 


declare @var int
--insert @var
declare @tvp tvp
--insert stuff (or not) into @tvp 
declare @tvpCount int = select count(*) from @tvp

SELECT distinct t.foo1, t.foo2, t.foo3
  FROM dbo.t t 
  join tvp
    on t.foo4 = @var
   and ( @tvpCount = 0 or t.foo1 = @tvp.foo1 )
+1


source






More articles:


All Articles