Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Post Unanswered: @@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,
    Parita

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    maybe a simpler test is in order...

    Code:
    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...
    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'
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    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

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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:
    Code:
    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.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •