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
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.
I believe I have found your problem. See code snippet below.
Code Snippet
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
BEGIN
raiserror('Account is not Approved',16,1)
select @.BuyerID
return
END
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
An IF statement will only apply to the statement immediately following it, unless that statement is BEGIN, then it will execute until the next END is found.
