|
lesponce
Starting Member
6 Posts |
 Posted - 02/06/2013 : 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.
|
|
SQL IF ELSE Statements, BEGIN/END, CASE
