need help [check my SP its not working fine]

[isa]

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.

[mikr0s]

...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 :

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--