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