Tyler
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].[Da teOfFirstE nrollment] <= @DateOfFirstjoin and
[database].[dbo].[user].[D ateOfDisen rollment] >= @DateOfDisjoin and
IF (@Submitter IS NOT NULL)
BEGIN
AND [database].[dbo].[user].[S ubmitter] IN (@Submitter)
END
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].[Da
[database].[dbo].[user].[D
IF (@Submitter IS NOT NULL)
BEGIN
AND [database].[dbo].[user].[S
END
A value list as in a delimited text string, or is @Submitter a table variable?
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.
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?
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'
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] + ',%')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.