asked on

I need help using IF ELSE in a SQL Server 2008 stored proc

Hi Experts,
I need help using an IF statement in my stored procedure.
I currently do a SELECT STATEMENT QUERY that I use top place values into variables. I want to do an if statement based on thge value of my @BillOption variable.

Here is my select statement

SELECT TOP(1) @Amount = O.ShipmentAmount,
@BillOPtion = O.BillOption
FROM Orders O
WHERE OrderNumber = @OrderNumber

I want my if statement to do the following:

IF my @BillOption = 'GROUND' I want my insert statment to look like this:

INSERT INTO OrderShipment(CustNumber, ShipmentAmount, OrderStatus)
SELECT CUSTNUMBER, SHIPAMOUNT, ORDERSTATUS)
FROM INTERORDERS
WHERE OrderNumber = @OrderNumber

ELSE
IF my @BillOption = 'COLLECT' I want my insert statment to look like this:

INSERT INTO OrderShipment(CustNumber, ShipmentAmount, OrderStatus)
SELECT CUSTNUMBER, @Amount, @BillOPtion)
FROM EXTERORDERS
WHERE OrderNumber = @OrderNumber

How can I do this? Thank you very much in advance.

mrotor

Jim Horn

That would be a couple of CASE blocks

INSERT INTO OrderShipment(CustNumber, ShipmentAmount, OrderStatus)
SELECT CUSTNUMBER, 
  CASE WHEN @BillOption = 'GROUND' THEN SHIPAMOUNT WHEN @BillOption = 'COLLECT' THEN @Amount END, 
  CASE WHEN @BillOption = 'GROUND' THEN ORDERSTATUS WHEN @BillOption = 'COLLECT' THEN @BillOPtion END, 
FROM EXTERORDERS
WHERE OrderNumber = @OrderNumber

Open in new window

Here's an article on SQL Server CASE Solutions that is a larger demo of CASE.

Guy Hengel [angelIII / a3]

The question is what do you want to do if @billion is neither of the 2 values...

As the FROM is different tables , you can use indeed if statment, or you could use a UNION ALL structure

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dukster131

You can just use two if statements - I like to bracket the information in a Begin End statement

IF my @BillOption = 'GROUND'
Begin
INSERT INTO OrderShipment(CustNumber, ShipmentAmount, OrderStatus)
SELECT CUSTNUMBER, SHIPAMOUNT, ORDERSTATUS)
FROM INTERORDERS
WHERE OrderNumber = @OrderNumber
End

IF my @BillOption = 'COLLECT'
Begin

INSERT INTO OrderShipment(CustNumber, ShipmentAmount, OrderStatus)
SELECT CUSTNUMBER, @Amount, @BillOPtion)
FROM EXTERORDERS
WHERE OrderNumber = @OrderNumber

End

mainrotor

ASKER

Duskter131,
What are the benefits of bracketing the information with Begin and End statements?

mrotor

Guy Hengel [angelIII / a3]

It's to make code more readable.
For a single statement it's not required.

Jim Horn

Thanks for the grade. Good luck with your project. -Jim