Search code examples
sql-servert-sqlconditional-statementstable-valued-parameterssql-in

T-SQL conditional AND statement

I'm trying to set up a stored procedure where I'm taking in a table value parameter and using it inside an IN statement to grab the results, the issue is the table value parameter can be empty, is there a way for me to conditionally insert an entire and 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 for conceptually, any help on what the proper way to do this?

Solution

  • This version assumes that results should still be returned even when @tvp is empty. It says that @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)
)