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

9 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?

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