asked on

IF, ELSE IF difficulties

Hi there!

I do not know how to use "ELSE IF" properly...

My situation is the following:

IF @MaterialID in (411, 412, 413, 414, 415) then I want to RETURN 1 , always.

ELSE, that is, if @MaterialID is not in the list above, and only in this case, then there is a decision to make:

If (@ param1 > @param2) then return 2
If not, that is, @MaterialID is not in the list above AND @param1 > @param2 is false, then return 3

Could someone put this in SQL form, using "ELSE IF" whenever possible, so that I learn the structure?

Thanks!
fskilnik.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

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

Start Free Trial

fskilnik

ASKER

Hi, ste5an!

Thank you for your very illuminating alternatives!

Regards,
fskilnik.

fskilnik

ASKER

This is what I could do through your suggestions:

CREATE FUNCTION [dbo].[StillAvailable](@FirstInput datetime, @SecondInput datetime, @MaterialID int)
RETURNS bit
AS
BEGIN
IF (@MaterialID IN (411, 412, 413, 414, 415))
BEGIN
  RETURN 1
END
ELSE
BEGIN
  IF (@FirstInput !> @SecondInput)
  BEGIN
    RETURN 1
  END
END
  RETURN 0
END

Open in new window

Just 0 and 1 were needed as RETURNs in the real scenario...

Thanks again!

ste5an

I would write it as:

CREATE FUNCTION dbo.StillAvailable ( @FirstInput DATETIME, @SecondInput DATETIME, @MaterialID INT )
RETURNS BIT
AS
BEGIN
    RETURN CASE
        WHEN @MaterialID IN (411, 412, 413, 414, 415) OR @FirstInput !> @SecondInput THEN 1
        ELSE 0
    END;
END

Open in new window

fskilnik

ASKER

Well, I will change right now, LoL ... thanks a lot!