SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements

Here is the question I received the other day in email.

“I have business logic in my .net code and we use lots of IF … ELSE logic in our code. I want to move the logic to Stored Procedure. How do I convert the logic of the IF…ELSE to T-SQL. Please help.”

I have previously received this answer few times. As data grows the performance problems grows more as well. Here is the how you can convert the logic of IF…ELSE in to CASE statement of SQL Server.

Here are few of the examples:

Example 1:

If you are logic is as following:

IF -1 < 1 THEN ‘TRUE’
ELSE ‘FALSE’

You can just use CASE statement as follows:

-- SQL Server 2008 and earlier version solution
SELECT CASE
WHEN -1 < 1 THEN 'TRUE'
ELSE 'FALSE' END AS Result
GO

-- SQL Server 2012 solution
SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;
GO

If you are interested further about how IIF of SQL Server 2012 works read the blog post which I have written earlier this year .

Well, in our example the condition which we have used is pretty simple but in the real world the logic can very complex. Let us see two different methods of how we an do CASE statement when we have logic based on the column of the table.

Example 2:

If you are logic is as following:

IF BusinessEntityID < 10 THEN FirstName
ELSE IF BusinessEntityID > 10 THEN PersonType
FROM Person.Person p

You can convert the same in the T-SQL as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
WHEN BusinessEntityID > 10 THEN PersonType END AS Col,
BusinessEntityID, Title, PersonType
FROM Person.Person p

However, if your logic is based on multiple column and conditions are complicated, you can follow the example 3.

Example 3:

If you are logic is as following:

IF BusinessEntityID < 10 THEN FirstName
ELSE IF BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType
ELSE IF Title = 'Mr.' THEN 'Mister'
ELSE 'No Idea'
FROM Person.Person p

You can convert the same in the T-SQL as follows:

SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName
WHEN BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType
WHEN Title = 'Mr.' THEN 'Mister'
ELSE 'No Idea' END AS Col,
BusinessEntityID, Title, PersonType
FROM Person.Person p

I hope this solution is good enough to convert the IF…ELSE logic to CASE Statement in SQL Server. Let me know if you need further information about the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

18 thoughts on “SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements

  1. Converting to store procedure is good idea.More detail is require.It depend upon example.
    If number of hit is more then may be create separate proc for each if -else block may help.
    This is very general.

  2. I have a question,

    my table have following data:
    backupID, startTime, EndTime
    —————————————
    101, 04/11/2013 11:00:00, 04/11/2013 11:55:00
    102, 04/11/2013 11:00:00, 04/11/2013 11:24:00
    103, 04/11/2013 11:20:00, 04/11/2013 11:45:00
    104, 04/11/2013 11:30:00, 04/11/2013 11:35:00
    105, 04/11/2013 11:40:00, 04/11/2013 11:55:00

    can I use the view to show the backup status in every 10 mins?

    I wonder the result as following:
    time, count
    ——————————
    04/11/2013 11:00:00, 2
    04/11/2013 11:10:00, 2
    04/11/2013 11:20:00, 3
    04/11/2013 11:30:00, 3
    04/11/2013 11:40:00, 3
    04/11/2013 11:50:00, 2
    04/11/2013 12:00:00, 0

    04/11/2013 11:00:00 – 04/11/2013 11:09:59 have 2 jobs, 101 & 102
    04/11/2013 11:10:00 – 04/11/2013 11:19:59 have 2 jobs, 101 & 102
    04/11/2013 11:20:00 – 04/11/2013 11:29:59 have 3 jobs, 101 & 102 & 103

    04/11/2013 11:50:00 – 04/11/2013 11:59:59 have 2 jobs, 101 & 105
    04/11/2013 12:00:00 – 04/11/2013 12:09:59 have 0 job

    I wonder if you can give me a help……thanks a lot

    Matt

  3. Hi Matt,

    Declare @back table (backupID int,startTime datetime,endTime datetime)
    insert into @back values
    (101, ’04/11/2013 11:00:00′, ’04/11/2013 11:55:00′),
    (102, ’04/11/2013 11:00:00′, ’04/11/2013 11:24:00′),
    (103, ’04/11/2013 11:20:00′, ’04/11/2013 11:45:00′),
    (104, ’04/11/2013 11:30:00′, ’04/11/2013 11:35:00′),
    (105, ’04/11/2013 11:40:00′, ’04/11/2013 11:55:00′)

    ;with CTE1 as
    (Select max(endtime)maxtime from @back)
    , CTE as
    (
    select min(startTime) minTime from @back

    union all
    Select DATEADD(minute,10,minTime) from cte
    where minTime<=(select maxtime from cte1)

    )
    select minTime,t4.cnt from CTE
    cross apply (select count(*) cnt from @back where minTime between startTime and endtime) t4

  4. Hi Matt,
    I don’t think that is best of solution.coz there can be lot of record.But above solution can be easily modify to display 10-20 record at a time and store the max value somewhere to get next 10-20 record for next time
    I don’t think view is require in this example.how you are planning ti display in front end.

  5. Hi pinal,

    How can we add the salary of the employee for all the columns and display his salary

    empid empname salary jansal feb march april may june july
    1 gjg 100 70 NULL 434 NULL 535 NULL NULL
    2 DF 200 NULL 345 NULL NULL NULL 435 NULL

  6. select peoplesoftprojectId from dbo.tbl_DAG_project_Level_Mappings s
    inner join @pracVerticalTable p on p.Name=’VerticalId’ and p.value=s.VerticalID
    where PracticeID=@practiceid
    and EligibleVH_Id=@practiceID
    and isnull(SubverticalName,”)=(Case @subvertical when ” then isnull(SubverticalName,”) else @subvertical end)
    and isnull(SubLevel1,”)=(case @sublevel1 when ” then isnull(SubLevel1,”) else @sublevel1 end)
    and isnull(SubLevel2,”)=(case @sublevel2 when ” then isnull(SubLevel2,”) else @sublevel2 end)
    and isnull(SubLevel3,”)=(case @sublevel3 when ” then isnull(SubLevel3,”) else @sublevel3 end)
    will it cause performance issue? as it contain only 300 rows i cannot check this performance…and can you pls tel how to check performance?

    • give example of what you want .you can proceed like this,

      ;With CTE as
      (SELECT CASE WHEN BusinessEntityID 10 AND Title IS NOT NULL THEN PersonType
      WHEN Title = ‘Mr.’ THEN ‘Mister’
      ELSE ‘No Idea’ END AS Col,
      BusinessEntityID, Title, PersonType
      FROM Person.Person p
      )
      ,CTE1 as
      (
      select BusinessEntityID,COUNT(*)Co1count
      from CTE
      group by col
      )

      select * from CTE1

  7. I am also trying to use an IF logic statement in a report but as an expression, I have been searching the net to help me find an answer as everything I have tried is sending an error when I run The report feeds from multiple tables and has various filters set of which I am extremely proud to say I figured out however I just can’t get figure out the right expression for this
    Fields are non-numeric
    Fields = Table3.Process & Table3.Processtwo
    equation = IF Process is Blank/Null then return Processtwo
    IF Process is Not Blank/Null the return Process/Processtwo

    if anyone has a suggestion I would be thankful !

    • Hey Shae –

      For SQL Server 2012+, you would want something like this:
      IIF(ISNULL(Process, ”) = ”, ProcessTwo, Process)

      but you would need to use a CASE for 2008 and below:
      CASE WHEN ISNULL(Process, ”) = ” THEN ProcessTwo ELSE Process END

  8. Thank you,

    I have SQL 2008, and I ended up using

    =IIF(FIELDS!PROCESS.VALUE =”” OR ISNOTHING(FIELDS!PROCESS.VALUE),FIELDS!PROCESSTWO.VALUE,FIELDS!PROCESS.VALUE + “/” + FIELDS!PROCESSTWO.VALUE)

    which seems to be working :)

    Shae

  9. Hello In the SQL Database dbo.Fee I have
    Id CaseId FeeType UnitCost
    1017796 2697789 29 50
    1018167 2697789 1 150
    1019493 2697789 15 30

    I want to write a sql query where I want it displayed as
    CaseId AdminFee SubFee ContFee
    2697789 50 150 30

    Any help would be appreciated
    Thanks

  10. Hello In the SQL Database dbo.Fee I have
    ———————————————————————–
    Id | CaseId | FeeType | UnitCost
    ———————————————————————–
    1017796 | 2697789 | 29 | 50
    1018167 | 2697789 | 1 | 150
    1019493 | 2697789 | 15 | 30
    ———————————————————————–

    I want to write a sql query where I want it displayed as
    ——————————————————————–
    CaseId | AdminFee | SubFee | ContFee
    ——————————————————————–
    2697789 | 50 | 150 | 30
    ———————————————————————
    Any help would be appreciated
    Thanks

    • Declare @t table(Id int, CaseId int, FeeType int, UnitCost int)
      insert into @t
      select 1017796 , 2697789 , 29 , 50
      union all
      select 1018167 , 2697789 , 1 , 150
      union all
      select 1019493 , 2697789 , 15 , 30
      –select * from @t

      SELECT CaseId,[29] AdminFee , [1] SubFee , [15] ContFee
      FROM
      (SELECT CaseId,FeeType,UnitCost FROM @t) p
      PIVOT
      (max(UnitCost) FOR FeeType IN
      ([29], [1], [15])
      )AS pvt;
      GO

  11. Hello i have a case , i’d like to create a new login based on the SQL server version i am running i have more than 300 instances (from SQL2000 to SQL2012)
    but because adding a login in SQL2000 and SQL2005+ are using different SQL statement
    i need to specify the SQL to execute for each version like following :

    Thanks for all your help

    If SQL version =2000
    then execute
    EXEC master.dbo.sp_addlogin @loginame = N’inventory’, @passwd = N’SQL1234′, @defdb = N’master’
    else
    If SQL version > 2000 then
    CREATE LOGIN [inventory] WITH PASSWORD=N’SQL1234′, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s