Link to home
Start Free TrialLog in
Avatar of Tyler
TylerFlag for United States of America

asked on

If in a where clause in t-sql

I have a procedure that has a condition that a variable @Submitter could be null, could have one value or could have a value list
in my SQL stament where clause i want to have it ignore the filter if it is null how can i do this?

select *
from  [databse].[dbo].[user]
Where
      [databse].[dbo].[user].[DateOfFirstEnrollment] <= @DateOfFirstjoin and
      [database].[dbo].[user].[DateOfDisenrollment] >= @DateOfDisjoin  and
     IF (@Submitter IS NOT NULL)
        BEGIN
           AND [database].[dbo].[user].[Submitter] IN (@Submitter)  
        END
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

A value list as in a delimited text string, or is @Submitter a table variable?
Avatar of Tyler

ASKER

@submitter is a velue or value list. passed through a stored procedure  it could be 'TEXT' or 'ONE,TWO,Three,Text' or null

so the 'IN' would work with the first 2 examples but the null i want to ignore alltogeather in the filter.
And what should happen if @Submitter is null? Should the query return nothing, or should it only consider the date criteria?
Avatar of Tyler

ASKER

if @Submitter has value it would use it as a filer if no value it would not use it as a filter

if @Submitter has no value or is 'all' it would ignor @submitter in the filter
  where  datestart='1/1/2017' and
                    dateend='1'5'2017'

if @Submitter has a single value it would us it varchar value
  where submitter = @submitter and
                    datestart='1/1/2017' and
                    dateend='1'5'2017'

if @Submitter has multi-value it would us it as a list
       where submitter in @submitter and
                    datestart='1/1/2017' and
                    dateend='1'5'2017'
select * 
from  [databse].[dbo].[user] u
Where 
      u.[DateOfFirstEnrollment] <= @DateOfFirstjoin and
      u.[DateOfDisenrollment] >= @DateOfDisjoin  and
      (@Submitter IS NULL OR
      ',' + @Submitter + ',' LIKE '%,' + u.[Submitter] + ',%')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial