mercredi 27 mai 2015

How to use IF ELSE in SQL statements

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

Declare @PlatinumLimitAirportLounge int

Declare  @PlatinumLimitRoadAssistance int

Declare @GoldLimitTaxi int

Declare @GoldLimitAirportLounge int

Declare  @GoldLimitRoadAssistance int

Set @PlatinumLimitTaxi=4;

Set @PlatinumLimitAirportLounge=4;

Set @PlatinumLimitRoadAssistance=4;

Set @GoldLimitTaxi=0;

Set @GoldLimitAirportLounge=2;

Set @GoldLimitRoadAssistance=0;

Declare @CardTypeId int

Declare @CardType int

Set  @CardTypeId = (Select Card.CardTypeId From Card Where Card.EmbossLine=@PAN)

if (@CardTypeId=1)

begin

Set @CardType=1

end 

else if (@CardTypeId=2)
begin
Set @CardType=2

End 

----------------------------------checking visits------------------------------------------------
Declare @VisitTaxi int

set @VisitTaxi = (Select SUM(Quantity) from POS_Transactions where (Product = '1' And PAN=@Pan))
Declare @VisitAirport int

set @VisitAirport = (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?

Aucun commentaire:

Enregistrer un commentaire