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
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.
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
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
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.
hi,
Is there any way to return two columns if condition is satisfied?
I think no.But you can concatenate both column .
What is the example like ?
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
How to skip If for the below example
if (object_id(‘tempdb..#table1’) is not null) drop table #table1
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?
I have a scenario where I need to group the output of the Case statement.. If there any option for that??
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
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
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
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
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
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
I need to create a table as shown below to filter the Names I am interested in and my input message contains different Class types and Model types for each Name.
Example Message:
John
A
DEFA
John
A
DEFB
John
A
DEFC
Elise
B
ABCD
Elise
B
ABCE
Elise
B
ABCF
Table to be created: (Currently Model column for Name is blank as I do not want to insert all possible Models for John in DB and instead accept all Models for this name)
Name Class Model
John A
Elise B ABCD
Inputs to Sql from message — [Name, class , Model] and need an equivalent sql case statement for below If condition.
—————————
if
Name=John and Class=A and Model=*(Anything from my input message)
then
Result is 1 valid Name
else
Result is 0 not valid name
fi
if
Name=Elise and Class=B and Model=ABCD
then
Result is 1 valid Name
else
Result is 0 not valid name
fi
—————————————————–
thanks in advance
Hi, This article is awesome i have query. Which is better IF-Else or Case in Performance Senario
@Jaydip – you need to test and find the answer.
showing syntax error in declaration. help me
CREATE PROCEDURE SearchEmpRecords_Sp
$SearchBy varchar(50),
$SearchVal varchar(50)
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
SELECT @sql=CASE $SearchBy
WHEN ‘Emp Name’ THEN
‘SELECT * FROM Emp_Tb WHERE EmpName LIKE ”’+ $SearchVal +’%”’
WHEN ‘City’ THEN
‘SELECT * FROM Emp_Tb WHERE City LIKE ”’+ $SearchVal +’%”’
WHEN ‘Salary’ THEN
‘SELECT * FROM Emp_Tb WHERE Salary = ‘ + $SearchVal + ”
ELSE
‘(SELECT * FROM Emp_Tb)’
END
END
EXECUTE sp_executesql @sql
you need to use @ not $
in ms sql this code is working perfect in my sql is showing syntax error.
if i am using @ means it is showing syntax error in this line itself
CREATE PROCEDURE SearchEmpRecords_Sp
$SearchBy varchar(50),
$SearchVal varchar(50)
if i am using $ means it is showing syntax error in this line
DECLARE @sql NVARCHAR(1000)
Please use MySQL Forums.
how write nested if else in select query?
If
begin
if
begin
end
end
else
begin
end
Hi,
I have a database table consists two column(column a and column b). I want to filter the table with given parameters(mya and myb) but mya and mayb should not be null so it is like;
SELECT t FROM LIST WHERE if mya != null then t.a=:mya AND if myb!=null t.b=:myb
how can i write the sentence?
Osman
Why Error
select L.[UserID],L.[UserGrade],L.[Operation],L.[OpDate],
case when L.[UserGrade]>1 then (select teacherNAME from Teacher ,[Log] where teacherID=[Log].[UserGrade] )
when L.[UserGrade]=1 then (select [Adm_Name] from Administrator ,[Log] where [Adm_ID]=[Log].[UserGrade] )
when L.[UserGrade]<1 then (select [studentNAME] from student ,[Log] where [studentID]=[Log].[UserGrade] )
end
from [Log] L,Teacher , Administrator ,student