How to use IF ELSE in SQL statements

Question

I have a stored procedure and I am trying to use IF ELSE conditions.

Alter procedure spInsertVirtualTransactions
(    
    @TerminalID varchar(8),    
    @Pan varchar(20),    
    @Product varchar(4)
)
AS

DECLARE @TERMINALNEWID VARCHAR(8)        

select @TERMINALNEWID=TERMINALID FROM TERMINAL WHERE TerminalID=@TerminalID  

Declare 
    @PlatinumLimitTaxi int = 4,
    @PlatinumLimitAirportLounge int = 4,
    @PlatinumLimitRoadAssistance int = 4,
    @GoldLimitTaxi int = 0,
    @GoldLimitAirportLounge int = 2,
    @GoldLimitRoadAssistance int = 0

Declare
    @CardTypeId int = (Select Card.CardTypeId From Card Where Card.EmbossLine=@PAN),
    @CardType int    

if (@CardTypeId=1)    
begin    
    Set @CardType=1    
end 
else 
begin
    if (@CardTypeId=2)
    begin
        Set @CardType=2
    End 
End
----------------------------------checking visits------------------------------------------------
Declare @VisitTaxi int = (Select SUM(Quantity) from POS_Transactions where (Product = '1' And PAN=@Pan))
Declare @VisitAirport int = (Select SUM(Quantity) from POS_Transactions where (Product = '2' And PAN=@Pan))


-----------------------------------taxi-----------------------------------------------------------
if (@Product ='1')
begin
    if (@CardType=1 and (@PlatinumLimitTaxi-@VisitTaxi) > 0)
    begin

         INSERT INTO pos_transactions        
         (TransactionDate, TerminalID, BatchNumber, SequenceNumber, Pan, TransactionTypeID, SettlementDate, Product, Quantity)
         VALUES        
         (GetDate(),@TERMINALNEWID, 0, -1, @Pan, 0, GETDATE(), @Product, 1)

         PRINT N'Platinum Taxi visits inserted.'
    end

    else    
    begin    
        Raiserror('Visits for Platinum Taxi have exceeded',16,1)    
        return    
    end

    if (@CardType=2 and (@GoldLimitTaxi-@VisitTaxi) > 0)    
    begin    
        INSERT INTO pos_transactions
        (TransactionDate, TerminalID, BatchNumber, SequenceNumber, Pan, TransactionTypeID, SettlementDate, Product, Quantity)       
        VALUES        
        (GetDate(), @TERMINALNEWID, 0, -1, @Pan, 0, GETDATE(), @Product, 1)

        PRINT N'Gold Taxi visits inserted.'
    end

    else    
    begin       
        Raiserror('Visits for Gold Taxi have exceeded the limit',16,1)    
        return      
    end
end

----------------------------------Airport-------------------------------------------------------
if (@Product='2')
begin
    if (@CardType=1 and (@PlatinumLimitAirportLounge-@VisitAirport) > 0)
    begin
        INSERT INTO pos_transactions        
        (TransactionDate, TerminalID, BatchNumber, SequenceNumber, Pan, TransactionTypeID, SettlementDate, Product, Quantity)       
        VALUES        
        (GetDate(), @TERMINALNEWID, 0, -1, @Pan, 0, GETDATE(), @Product, 1) 

        PRINT N'Platinum Airport visits inserted.'
    end

    else    
    begin

        Raiserror('Visits for Platinum Airport Lounge have exceeded',16,1)
        return

    end

    if (@CardType=2 and (@GoldLimitAirportLounge-@VisitAirport) > 0)
    begin
        INSERT INTO pos_transactions        
        (TransactionDate, TerminalID, BatchNumber, SequenceNumber, Pan, TransactionTypeID, SettlementDate, Product, Quantity)
        VALUES
        (GetDate(), @TERMINALNEWID, 0, -1, @Pan, 0, GETDATE(), @Product, 1)

        PRINT N'Gold Airport visits inserted.'
    end

    else    
    begin

        Raiserror('Visits for Gold Airport Lounge have exceeded the limit',16,1)
        return
    end
end 

Suppose I call my Stored Procedure with the following arguments

spInsertVirtualTransactions '00000001','22010402','2'

I should only get

Platinum Airport visits inserted

But it gets into the second IF block too so alongwith the above message I get this message too

Visits for Gold Airport Lounge have exceeded the limit

Which means its getting into the second IF block too. How can I improve this condition. Can I use an ELSE IF?

Answer 1

sorry syntax for if..elseif..else in sql server is

IF
Begin
....
END
ELSE IF
Begin
......
END
Else
...
END

In Your program

if (@Product ='1') 
    Begin
      if (@CardType=1) 
      Begin
         if (@PlatinumLimitTaxi-@VisitTaxi) > 0) 
         Begin
            ……..
         end
         else
         begin
                 ………….
         end
      end
     Else if (@CardType=2)
      begin
        if (@GoldLimitTaxi-@VisitTaxi) > 0) 
        Begin
         ………..
       end
        else
        begin
        ………
        end
      End
    End

----------------------------------Airport-------------------------------------------------------
if (@Product='2') 
Begin
    if (@CardType=1)
    Begin
      if (@PlatinumLimitAirportLounge-@VisitAirport) > 0)
      begin
      ……
     end
      Else
      begin
      ….
      End
   end

  Else if (@CardType=2)
  begin
      If (@GoldLimitAirportLounge-@VisitAirport) > 0)
      Begin
       …….
     end
     Else
     begin
       …..
      end
   end
end