SQL IF ELSE Statements, BEGIN/END, CASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

SQL IF ELSE Statements, BEGIN/END, CASE

Author

Topic

lesponce
Starting Member

8 Posts

Posted - 2013-02-06 : 22:05:06

I'm trying to determine what's the best way to have multiple if/else statments in a stored procedure.
I got a web page application that will allow the user to search customer info based on two joined tables.
With that said, I was using inline code eg. string sql = "Select field1, field2, field3 from Table" and blah blah..

I was told that the best practice is to use a stored procedure instead of having the sql string on the asp.net application.

Ok, so working on the stored procedure.... is this a good practice? see below....

Declare @query varchar(2000)

Set @query =

'Select
field1, field2, field3, field4
from table1
inner join table2
on field = field
where field = '''something'''' and '

-- here's where I'd like to have some feedback... (values will be displayed on a gridview)

-- SAMPLE 1

if @First <> ''
set @query = query + ' field = @First
else
if @Last <> ''
set @query = @query + ' field2 = @Last
else
if @address <> ''
set @query = @query + ' field3 = @address
else
if @address2 <> ''
set @query = @query + ' field3 = @address2 // and so on...

-- SAMPLE 2
-- I don't think I need BEGIN/END right?
if @First <> ''
set @query = query + ' field = @First
else
BEGIN
if @Last <> ''
set @query = @query + ' field2 = @Last
END
else
if @address <> ''
BEGIN
set @query = @query + ' field3 = @address
END
else
BEGIN
if @address2 <> ''
set @query = @query + ' field3 = @address2 // and so on...
END

-- SAMPLE 3 Should I use CASE instead? if so, why? How? Any other way better than the ones above?

Which way is better taking into consideration code, style, performance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 05:08:45

I would second your information that a stored procedure instead of adhoc SQL statements constructed from user input is better. Avoiding SQL injection attacks is one reason, there are others as well.

A good example of how to do what you are attempting to do efficiently and in a manner that is not susceptible to SQL injection attacks is described in Gail Shaw's article at this link: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

lesponce
Starting Member

8 Posts

Posted - 2013-02-07 : 07:14:50

Thanks James K for the feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources