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 (https://blog.sqlauthority.com)
33 Comments. Leave new
case when SIGN([YTD Primary Measure Attainment]) <0 then '0-'
when [YTD Primary Measure Attainment] <= 0.1 then '0-10'
when [YTD Primary Measure Attainment] <= 0.2 then '10-20'
when [YTD Primary Measure Attainment] <= 0.3 then '20-30'
when [YTD Primary Measure Attainment] <= 0.4 then '30-40'
when [YTD Primary Measure Attainment] <= 0.5 then '40-50'
when [YTD Primary Measure Attainment] <= 0.6 then '50-60'
when [YTD Primary Measure Attainment] <= 0.7 then '60-70'
when [YTD Primary Measure Attainment] <=0.8 then '70-80'
when [YTD Primary Measure Attainment] <=0.9 then '80-90'
when [YTD Primary Measure Attainment] <= 1.0 then '90-100'
when [YTD Primary Measure Attainment] <= 1.1 then '100-110'
when [YTD Primary Measure Attainment] <= 1.2 then '110-120'
when [YTD Primary Measure Attainment] <=1.3 then '120-130'
when [YTD Primary Measure Attainment] <= 1.4 then '130-140'
when [YTD Primary Measure Attainment] 1.5 then ‘150+’
else ‘0’
end as ‘Performance range’
This Case in my select statement gives me:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
The Query Runs Perfectly fine when the case is removed from my select statement.
Is there a better way of re-writing the case ? Why do I get Such an error because of a Simple case? I don’t see any complexity in the statement.
Looks like this CASE.. WHEN is a part of already complex query (mostly a view).
Why don’t you first dump all of this in temporary table and then use CASE.. WHEN only for that column?
How would I write a CASE statement where I need to check the value of a field first before I know which way I need the CASE statement prepared? I wrote it as an IF below:
select *,
IF schedule_at is not NULL THEN
CASE when actual_start < schedule_at THEN 'Early"
else 'Late'
END as Delivery,
ELSE
case when actual_start between schedule_from and schedule_to THEN 'On Time'
when datediff(minute,actual_start,schedule_from) < 0 THEN 'Late'
else 'Early'
END as Delivery,
END
from lennox_dates
I really appreciate your posts they are so helpful. Thank you!
I have created a report with courses in one column, and the teacher that teaches them in the other column using (STUFF FOR XML PATH). The problem is, if a student takes two classes from the same teacher, the teacher’s name needs to show twice in the Teacher column to correlate with the courses (course 1 and 2 taught by teacher 1, and course 3 and 4 taught by teacher 2) as shown below. Is this possible, or am i just reaching for the impossible?
Course column: English I | Creative Writing | English II | Creative Writing II
Teacher column: jeffreyjc | jeffreyjc | lainmw | lainmw