locked
SQL IF ELSE Statements, BEGIN/END, CASE RRS feed

  • Question

  • User2012297617 posted

    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.
    

    Wednesday, February 6, 2013 9:58 PM

Answers

  • User-972560101 posted

    Both scenario will working correct.  But instead of using join, you have use temp table to filter the records from the first table and then second table.

    It will be very fast even though bulk of datas in the database.  Best practices is to put BEGIN and END method.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 11:50 PM
  • User143067745 posted
    if @First <> ''
    
    Begin
       set @query1 = @query + ' field =  @First;
    set @query2=@query+ field2;
    ---------
    -------
    ---------
    End
    Else
    Begin
    -------
    --------
    End
    Thats when Begin/end will be necessary. 
    When you have to execute more than one statement within a if/Else block then it is required to use Begin/End.



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2013 12:13 AM

All replies

  • User-972560101 posted

    Both scenario will working correct.  But instead of using join, you have use temp table to filter the records from the first table and then second table.

    It will be very fast even though bulk of datas in the database.  Best practices is to put BEGIN and END method.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 11:50 PM
  • User143067745 posted
    if @First <> ''
    
    Begin
       set @query1 = @query + ' field =  @First;
    set @query2=@query+ field2;
    ---------
    -------
    ---------
    End
    Else
    Begin
    -------
    --------
    End
    Thats when Begin/end will be necessary. 
    When you have to execute more than one statement within a if/Else block then it is required to use Begin/End.



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2013 12:13 AM
  • User2012297617 posted

    Thanks for your feedback.  Yes, the code is working. I just wanted to get some input for best performance. Thx guys.

    Thursday, February 7, 2013 7:12 AM
  • User-972560101 posted

    Please mark as answer if it useful and can ready to help

    Thursday, February 7, 2013 7:17 AM