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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 SQL IF ELSE Statements, BEGIN/END, CASE
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lesponce
Starting Member

8 Posts

Posted - 02/06/2013 :  22:05:06  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/07/2013 :  05:08:45  Show Profile  Reply with Quote
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/
Go to Top of Page

lesponce
Starting Member

8 Posts

Posted - 02/07/2013 :  07:14:50  Show Profile  Reply with Quote
Thanks James K for the feedback.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000