Alternatives to IF ELSE in stored procedure SQL Server

sql, sql-server, stored-procedures

Solution

One way to do this (although it might possibly result in a worse query plan):

Select *
FROM FD.T fd 
INNER JOIN AD.T ad on fd.OCD = ad.ACD 
WHERE fd.SG BETWEEN @startdate AND @enddate 
      AND ad.Indicator = CASE 
                              WHEN @source = 'Not Air' THEN 'False'
                              WHEN @source = 'Air' THEN 'True'
                              ELSE ad.Indicator
                         END

[Whether that generates a better or worse plan remains to be seen...]

In addition, please note:

Using SELECT * is not recommended. Use an explicit column list

A column containing the string values 'True' and "False' would be better as a bit column

Be careful if your dates have a time portion. If they do, there are several ways to handle this, perhaps the most straightforward way is to cast to Date data type (SQL Server 2008 onwards):

WHERE CAST(fd.SG as Date) BETWEEN @startdate AND @enddate

But be aware that this might invalidate the use of an appropriate index. (Also assumes that `@startDate` and `@enddate` do not have a time portion)

Problem

I am new to stored procedures and still learning, below is what I tried and is working also. But, I have only one change on `ad.Indicator` and I am writing the same query thrice in `IF, ELSE IF`. Is there a better way to do this? (I am using SQL Server) ``` ALTER PROCEDURE TestingSP @startdate Datetime, @enddate Datetime, @source Varchar(10) AS BEGIN IF(@source = 'None') SELECT * FROM FD.T fd INNER JOIN AD.T ad ON fd.OCD = ad.ACD WHERE fd.SG BETWEEN @startdate AND @enddate ELSE IF(@source = 'Air') SELECT * FROM FD.T fd INNER JOIN AD.T ad ON fd.OCD = ad.ACD WHERE fd.SG BETWEEN @startdate AND @enddate AND ad.Indicator = 'True' ELSE IF(@source = 'Not Air') SELECT * FROM FD.T fd INNER JOIN AD.T ad ON fd.OCD = ad.ACD WHERE fd.SG BETWEEN @startdate AND @enddate AND ad.Indicator = 'False' END ```

Original source