CODEWITHSUNDEEP
sqlsql-serverstored-procedures
stig
stig

Reputation: 1220

SQL Stored Procedure Case When

Is something like this possible to do in a SQL stored procedure? Or do I have to have 3 seperate procedures? This works seperatly but now I only get an error: Incorrect syntax near the keyword 'between'.

FROM table a

WHERE
field1 = 'asd'
and field2 is null
and field3  not in ('a','b','c') 
and

case @input

when 'now' then
    (a.datefield between dateadd(day, -31, getdate()) and getdate())
when '24_hour' then
    (a.datefield between getdate() and dateadd(hour, 24, getdate()))
when '3_days' then
    (a.datefield between getdate() and dateadd(day, 3, getdate()))
end

order by a.datefield asc
end

Upvotes: 1

Views: 141

Answers (2)

Jon Egerton
Jon Egerton

Reputation: 41539

There are a few ways to achieve this. The following is one possibility:

case
    when @input ='now' and (a.datefield between dateadd(day, -31, getdate()) and getdate()) then 1
    when @input ='24_hour' and (a.datefield between getdate() and dateadd(hour, 24, getdate())) then 1
    when @input ='3_days' and (a.datefield between getdate() and dateadd(day, 3, getdate())) then 1
    else 0        
end = 1

For max scalabilty I'd probably look at a TVC though:

FROM table a
join (values 
        ('now',dateadd(day, -31, getdate()),getdate()),
        ('24_hour',getdate(),dateadd(hour, 24, getdate())),
        ('3_days',getdate(),dateadd(day, 3, getdate()))
     ) t(input,startdate,enddate) 
on t.input = @input
WHERE
field1 = 'asd'
and field2 is null
and field3  not in ('a','b','c') 
and a.datefield between t.startdate and t.enddate

Upvotes: 3

M.Ali
M.Ali

Reputation: 69504

You can also replace the case statement altogether and only use AND and OR operators inside you where clause. something like this.... 

FROM table a

WHERE field1 = 'asd'
and   field2 is null
and   field3  not in ('a','b','c') 
and 
   (
    (@input ='now'     AND a.datefield between getdate()-31 and getdate())
    OR
    (@input ='24_hour' AND a.datefield between getdate() and dateadd(hour, 24, getdate()))
    OR
    (@input ='3_days'  AND a.datefield between getdate() and  getdate()+3)
   )
order by a.datefield asc

Upvotes: 1

Related Questions