SQL Server Forums
Profile | Register | 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
 New Topic  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

3589 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000