@@rowcount in SQL server 2000

Hi,

We have migrated a stored procedure from SQL 7 to SQL 2000 . We made some changes to remove the Set rowcount and replaced it by rowcount function. But @.@.ROWCOUNT does not return the correct value. It returns 0 when it should return 1.

Here is a section of the code:
DECLARE @.no_of_rows int
DECLARE @.curr_prnt_cmp_convert char(5)

SELECT @.local_cmps_loop = 1,
@.master_child_loop = 1,
@.acct_list_loop = 1,
@.master_child2_loop = 1,
@.no_of_rows = 0,
@.curr_prnt_cmp_convert = ' '

SET NOCOUNT ON

/* -- Temporary Tables Creation -- */

CREATE TABLE #local_cmps (cmp_int smallint, org_flg tinyint, pflg tinyint)

CREATE TABLE #master_child(bus_int smallint, prnt_cmp_int smallint, chld_cmp_int smallint, pflg tinyint)

CREATE TABLE #acct_list (bus_int smallint, prnt_cmp_int smallint, acct_int int)

/* -- Insert all Local Components -- */

INSERT INTO #local_cmps (cmp_int, org_flg, pflg)
(SELECT DISTINCT cmp_int, origin_flg, 0
FROM tbl_sd_components
WHERE origin_flg = 1)

/* -- Get Fiscal Year from Data Base -- */

SELECT @.fiscal_year=num_data
FROM tbl_sd_sys_control
WHERE sys_ctl_typ = 1

/* -- Start Buiding Tables for Local Components -- */

WHILE (@.local_cmps_loop = 1)
/* WHILE ((SELECT COUNT(*) FROM #local_cmps WHERE pflg = 0) > 0) */
BEGIN

/*LEAPMIGRA : Commented and added TOP 1 to subsequect SELECT ; set rowcount 1 */

/* -- Select the Unprocessed Parent Component -- */

SELECT TOP 1 @.curr_prnt_cmp = cmp_int
FROM #local_cmps
WHERE pflg = 0

SELECT @.no_of_rows = @.@.ROWCOUNT

IF (@.no_of_rows = 0)
BREAK /* WHILE (@.local_cmps_loop = 1)*/

The value of @.no_of_rows should be 1. It gives 0
Please advise.

Thanks and Regards,
Paritamaybe a simpler test is in order...

DECLARE @.no_of_rows int
set nocount on
select top 1 au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows TOP 1'

select au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows'

and more to your code...
declare @.no_of_rows int
declare @.Tbl table(au_id varchar (11), au_lname varchar (40), phone char (12), contract bit)

set nocount on

insert into @.Tbl
select au_id,au_lname,phone,contract from pubs.dbo.authors
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows inserted'

select top 1 * from @.Tbl
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows TOP 1'

select * from @.Tbl
set @.no_of_rows = @.@.rowcount
select @.no_of_rows as '@.no_of_rows'|||Hi Paul,
Thanks for your reply. I did test this procedure by executing some print commands after fetching @.@.rowcount.

I also executed a dummy procedure with @.@.ROWCOUNT and it worked. But the migrated code (i posted earlier) does not give the right result.

Maybe you could give me some idea about situations where @.@.rowcount could fail.

Thanks and Regards,
Parita|||I honestly have never seen @.@.rowcount fail.

I have seen code where the developer thinks @.@.rowcount faild but it turned out that the query was not returning or updateing the number of rows the developer thought would be effected.

The other situation I have seen is that @.@.rowcount is not caputered right after the query is executed or @.@.rowcount is captured and the developer printed @.@.rowcount rather than the local variable holding the rowcount for the query in question!

here is what I would do to start debugging this problem:
DECLARE @.no_of_rows int
DECLARE @.curr_prnt_cmp_convert char(5)

-- ---------------------------
-- added by psy
-- ---------------------------
declare @.Debug tinyint
, @.RowCount int
-- ---------------------------

SELECT @.local_cmps_loop = 1,
@.master_child_loop = 1,
@.acct_list_loop = 1,
@.master_child2_loop = 1,
@.no_of_rows = 0,
@.curr_prnt_cmp_convert = ' '

-- ---------------------------
-- added by psy
-- ---------------------------
set @.Debug = 1
-- ---------------------------

SET NOCOUNT ON

/* -- Temporary Tables Creation -- */
CREATE TABLE #local_cmps (cmp_int smallint, org_flg tinyint, pflg tinyint)
CREATE TABLE #master_child(bus_int smallint, prnt_cmp_int smallint, chld_cmp_int smallint, pflg tinyint)
CREATE TABLE #acct_list (bus_int smallint, prnt_cmp_int smallint, acct_int int)

/* -- Insert all Local Components -- */
INSERT INTO #local_cmps (cmp_int, org_flg, pflg)
SELECT DISTINCT cmp_int, origin_flg, 0
FROM tbl_sd_components
WHERE origin_flg = 1

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('Records inserted #local_cmps @.RowCount:%d',0,1,@.RowCount) with nowait
-- ---------------------------

/* -- Get Fiscal Year from Data Base -- */
-- ---------------------------
-- This can be unpredictable! @.fiscal_year will hold the first value found in the num_data
-- attribute where sys_ctl_typ = 1. There is no guarente you will get the same result twice
-- if there are several records that meet the where clause!
-- ---------------------------
SELECT @.fiscal_year = num_data
FROM tbl_sd_sys_control
WHERE sys_ctl_typ = 1

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('After Select @.fiscal_year @.RowCount:%d',0,1,@.RowCount) with nowait
-- ---------------------------

/* -- Start Buiding Tables for Local Components -- */
WHILE (@.local_cmps_loop = 1)

/* WHILE ((SELECT COUNT(*) FROM #local_cmps WHERE pflg = 0) > 0) */
BEGIN

/*LEAPMIGRA : Commented and added TOP 1 to subsequect SELECT ; set rowcount 1 */
/* -- Select the Unprocessed Parent Component -- */

-- ---------------------------
-- added by psy
-- ---------------------------
set @.RowCount = @.@.rowcount

if (@.Debug <> 0)
raiserror('@.local_cmps_loop:%d, @.master_child_loop:%d, @.acct_list_loop:%d, @.master_child2_loop:%d, @.no_of_rows:%d, @.curr_prnt_cmp_convert:%s',
0,1,@.local_cmps_loop, @.master_child_loop, @.acct_list_loop, @.master_child2_loop, @.no_of_rows, @.curr_prnt_cmp_convert) with nowait
-- ---------------------------

SELECT TOP 1 @.curr_prnt_cmp = cmp_int
FROM #local_cmps
WHERE pflg = 0
SELECT @.no_of_rows = @.@.ROWCOUNT

IF (@.no_of_rows = 0)
BREAK /* WHILE (@.local_cmps_loop = 1)*/

-- ---------------------------
-- added by psy
-- ---------------------------
end
-- ---------------------------

I would bet the problem is some where in your code or your assumptions of the data or how the code is working! I don't see anything inherently wrong with your code but you have not posted the entire sp either.|||Hi Paul,

I have attached here the procedure code. The solution i found was to have a select count query instead of @.@.rowcount and it is working now.

But why @.@.rowcount would not work is a big dilemma.

Thanks

Parita