-
need help [check my SP its not working fine]
Hello everyone, im using SQL Server 2000, here is my table:
Table1: Buyers
BuyerID int
ParentID int
MerchantCode varchar(20) (each buyer has unique merchant code we say that its login is Merchant Code)
PinCode (this is used as a password for the buyers login)
ApprovalStatusCode int (FK, data is for authentication:Pending Approval, Approved, Cancelled)
IsCliEnabled smallint
Clis varchar(100) (this is the CSV: 12345, 2346,....)[as each buyer has more that 1 CLi values so this field is in CSV form data)
//Now buyers also has SubAccounts (Table2) that was made for his workers or some one else (Not necessary every buyer has SubAccounts)
Table2: Buyers SubAccounts
BuyerID
AccountNumber smallint
PinCode varchar(64)
CreateDate datetime
now here is my SP,
CREATE PROCEDURE IvrAuthenticateBuyer
@MerchantCode varchar(20),
@PinCode varchar(64),
@CLI varchar(15)
AS
-- For testing i give values at here
declare @MerchantCode varchar(20)
set @merchantCode='000000010'
declare @PinCode varchar(64)
set @PinCode='1234656'
declare @CLI varchar(15)
set @CLI='12345'
----------------------------------------
declare @BuyerID int
declare @ApprovalStatusCode smallint
declare @IsCliEnabled smallint
declare @Clis varchar(1000)
-- buyerID get by checking only 8 digits of merchant code (omit last one)
-- if last digit of merchant code are > "0" then
-- get them in a variable
-- check if the account is approved
-- check if CLI is enabled, if yes, check if @cli is in the list
SELECT @BuyerID = BuyerID, @ApprovalStatusCode = ApprovalStatusCode,
@IsCliEnabled = IsCLIEnabled, @Clis = coalesce(@Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @MerchantCode
--select @BuyerID
--select @IsCliEnabled
--select @ApprovalStatusCode
--select @Clis
-- chk all conditions
if @ApprovalStatusCode <> 2
raiserror('Account is not Approved',16,1)
select @BuyerID
return
if (@IsCliEnabled=1) --check whether true
begin --Main Begin
--charindex will return value greater than 0 if CLI is found in list
-- if charindex('34534',@Clis)>0
if ','+@Clis+',' like '%,'+@CLI +',%'
begin
print 'CLI found in CSV list'
end
else
begin
raiserror('CLI NOT found in CSV list',16,1)
end
end --Main End
else
begin
raiserror('CLI NOT ENABLED',16,1)
end
-- Get Last Digit of Merchant Code and stored them in a variable
declare @SubAccountNo varchar(2)
select @SubAccountNo = Substring(@MerchantCode,8,1)
-- Check It is SubAccount or the Buyer's Main Account
if convert(int, @SubAccountNo) > 0
select 1
from BuyerSubAccounts
where PinCode=@PinCode
else
select 1
from Buyers
where PinCode=@PinCode
that is used for Buyer Authenticate, but i dont know whats wrong with this as its not working fine now,
as if i give wrong password then it also retuns the error of if i give wrong CLi same its runs without giving any error. Kindly
check it whats wrong with this as i m in much trouble still can't find out the actual problem in my SP. So i requested to all of u
plz help me and make my SP correct.
Thanx in Advance.
-
...as if i give wrong password then it also retuns the error of if i give wrong CLi ...
Probably this should be ...as if i give wrong MerchantCode then it also returns ....., since the Password as stated is the @Pincode that is not used in any "control" statement...
(unless the statement that retrieves values ...FROM Buyers.. contains the @Pincode but is not displayed in your post:
SELECT @BuyerID = BuyerID, @ApprovalStatusCode = .....
FROM Buyers
WHERE MerchantCode = @MerchantCode
AND Pincode = @Pincode)
So, when an invalid @MerchantCode (a code that does NOT exist in the db) or a wrong @Pincode (in case @Pincode is used in the variables retrieval) are provided, then the following statement :
Code:
SELECT @BuyerID = BuyerID, @ApprovalStatusCode = ApprovalStatusCode,
@IsCliEnabled = IsCLIEnabled, @Clis = coalesce(@Clis+',','')+CLIs
FROM Buyers
WHERE MerchantCode = @MerchantCode
AND Pincode = @PinCode
returns NO result, thus all variables @BuyerID , @ApprovalStatusCode, @IsCliEnabled, @Clis are NULL.
These NULL values are causing the "trouble" in combination with the SET ANSI_NULLS setting that is probably ON.
Having NULL @variable values and SET ANSI_NULLS ON here is the flow:
if @ApprovalStatusCode <> 2 --> FALSE (so, no 'Account is not Approved' error is raised)
if (@IsCliEnabled=1) --> this is False, so skip all controls
begin --Main Begin
...................................
end --Main End
else --> the following code of control statement is executed
begin
raiserror('CLI NOT ENABLED',16,1)
end
In conclusion, when an invalid @MerchantCode (and/or and invalid @PinCode) is provided, then procedure returns CLI NOT ENABLED.
Option 1:
Place a control statement after retrieving variables:
IF @BuyerID IS NULL
BEGIN
raiserror('Invalid Account or Password',16,1)
return
END
and handle cases of NULL values:
if ISNULL(@ApprovalStatusCode, 0) <> 2
if (ISNULL(@IsCliEnabled, 0) =1) ...and so on.
Option 2:
Initialize variables before the SELECT ... FROM Buyers statement.
SELECT @ApprovalStatusCode = 0, @IsCliEnabled = 0, @Clis = ''
SELECT ... FROM Buyers
Control statement of @BuyerID value
--HTH--
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|