Conditional Logic in T-SQL

On March 21, 2010, in SQL, by Anuj Gakhar

I am sure this is a pretty common scenario in every developer’s coding journey. So I thought I would write up a little post mainly to remind me different ways of doing this as I always end up searching for it whenever I need this.

Lets say you have a stored procedure that takes input parameters and runs a query based on those parameteres. To keep things simple, I will work with only one parameter in this example. So, here is the basic stored procedure

[sql]
CREATE Procedure sp_users
(
@username varchar(20) = null
)
AS
Select * from tblUser where username = @username
[/sql]

As you can tell, this is the most simplest form of a SP. If you run this SP with an input parameter, it would return the row with that username.

Now, what should ideally happen is, the where clause “where username = @username” should only be applied if there was an input parameter supplied. Otherwise it should return all rows from the table.
One way of doing that is Dynamic SQL. Something like this :-

[sql]
CREATE Procedure sp_users
(
@username varchar(20) = null
)
AS
Declare @sqlstring varchar(200)
set @sqlstring = ‘select * from tblUser’
if (not @username is null)
begin
set @sqlstring = @sqlstring + ‘ where username =”’ + @username + ””
end
exec (@sqlstring)

[/sql]

The above code generates the Query String as a concatenated String and then finally executes the query using EXEC. However, I dont like Dynamic SQL because imagine if this was a query with 6 Joins and 8 parameters. It would be so hard to maintain the string concatenation.
Well, there is a better way of doing it. We can use a CASE statement as well. Like this :-

[sql]
CREATE Procedure sp_users
(
@username varchar(20) = null
)
AS
select * from tblUser
where username = CASE when @username is null then username else @username end
[/sql]

This seems to be the best solution so far. Well, my personal favorite is the one listed below. It uses a OR instead of CASE.

[sql]
CREATE Procedure sp_users
(
@username varchar(20) = null
)
AS
select * from tblUser
where (@username is null or username = @username)
[/sql]

The above code doest use any CASE or Dynamic SQL but still does the job and is the most efficient and easy to read as well.

Tagged with:  

One Response to Conditional Logic in T-SQL

  1. […] Conditional Logic in T-SQL | Anuj Gakhar’s Blog […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: