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.