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

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.

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply