Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011

    Unanswered: Using 'IF' in a stored procedure


    I have minimal SQL experience and no stored procedure experience. Here's the issue.

    I'm being asked to create a stored procedure with three different parameters; date, ordernumber and customernumber. The user would supply a value for one of these, and that determines the records the user would see. For instance, the user could input a customernumber of '283' and the results would show order information for that customer.

    So how does one typically structure something like this? Here is what I was playing around with:

    - define the three parameters with a default value of NULL
    - add error handling in the event that one parameter is not defined
    - IF customerNumber <> NULL THEN
    select query WHERE customerNumber = @customerNumber
    END IF
    - (repeat previous IF statement for the other two parameters)

    I just want to know what others would do in this situation. Is this a good structure?

    Advice is greatly appreciated.


  2. #2
    Join Date
    Sep 2011
    Greenville, SC USA

    RE: Using 'IF' in a stored procedure

    Would the same columns be returned regardless of parameter selection?
    If so, something like the following may be worth considering:

    select cst.* ,ord.*
      (select CustomerNumber ,OrderNumber ,* -- Other Fields
       from Orders (nolock)
       where CustomerNumber = @customerNumber -- By CustomerNumber
       select CustomerNumber ,OrderNumber ,* -- Other Fields
       from Orders (nolock)
       where orderDate = @Date -- By Date
       select CustomerNumber ,OrderNumber ,* -- Other Fields
       from Orders (nolock)
       where OrderNumber = @orderNumber -- By OrderNumber
      ) ord
     ,Customer cst (nolock)
      cst.CustomerNumer = ord.CustomerNumber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts