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
ending 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 inCode=@PinCode">PinCode=@PinCode
else
select 1
from Buyers
where inCode=@PinCode">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.
-
There is a RETURN above the line "if (@IsCliEnabled=1) --check whether true" stopping the procedure from going any further. This could be from your testing.
You also don't have any check of the PinCode against the MerchantAccount... SELECT 1 will just produce a 1, in the case of an incorrect PinCode no row is returned. This is working okay as far as I've tested.
Also, the Cli is checked before the Pincode so any error connected to the Cli will be produced as it is run before the PinCode check.
If none of the above helps, can you give us exactly what the procedure is supposed to do? That'll help determine the order of statements and checks.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply