CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Vbp
Vbp

Reputation: 1962

Alternatives to IF ELSE in stored procedure SQL Server

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

Upvotes: 4

Views: 5941

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

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)

Upvotes: 6

Related Questions