Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

SQL IF Statement or Case?

I am writing a SPROC.  Example below.

CREATE PROC ShippingUpdate
                  @ID INT,
                  @Packed BIT = NULL,
                  @MadeReady BIT = NULL,
                  @Shipped BIT = NULL,
                  @User VARCHAR(100),
                  @UserID INT

So based on @Packed, @MadeReady, and @Shipped I need to update certain fields as below.

Update Shipping
SET

IF @Packed = 1 Then
  BEGIN
	PackedBy = User,  PackedByID = UserID,  PackedDate = GetDate()
  END
 ELSE
  BEGIN
	PackedBy = NULL,  PackedByID = NULL,  PackedDate = NULL
  END

IF @MadeReady = 1 Then
  BEGIN
	MadeReadyBy = User,  MadeReadyByID = UserID,  MadeReadyDate = GetDate()
  END
 ELSE
  BEGIN
	MadeReadyBy = NULL,  MadeReadyByID = NULL,  MadeReadyDate = NULL
  END

IF @Shipped = 1 Then
  BEGIN
	ShippedBy = User,  ShippedByID = UserID,  ShippedDate = GetDate()
  END
 ELSE
  BEGIN
	ShippedBy = NULL,  ShippedByID = NULL,  ShippedDate = NULL
  END

Open in new window


Am I on the right track?  How do I get this to work?
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

There are two ways:
1- IF
IF @Packed = 1 Then
BEGIN
    UPDATE Shipping SET PackedBy = User, 	PackedByID = UserID, 	PackedDate = GetDate()
END
ELSE
BEGIN
    UPDATE Shipping SET PackedBy = NULL, 	PackedByID = NULL,  PackedDate = NULL
END

Open in new window

2- CASE
IF @Packed = 1 Then
UPDATE Shipping SET PackedBy = CASE WHEN @Packed=1 THEN User ELSE NULL END, 	PackedByID = CASE WHEN @Packed=1 THEN UserID ELSE NULL END, 	PackedDate = CASE WHEN @Packed=1 THEN GETDATE() ELSE NULL END

Open in new window

The first is easier for longer statements.
In the case code, disregard the first line. It was a left over from copy/paste. The correct code would be only line 2
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CipherIS

ASKER

Thanks - just what I needed
How is that any different from my solution? Either of mine would work, but my second solution is the same as above