Why two calls to @@rowcount returns two different values?

Question

I have this stored procedure to save values across two tables ACDPrograms and DefaultCourses. Here is my global parameters

 create proc USPSvUpdtACDProgramFromTypes
    @pPRGCode char(10), 
    @pPRGName varchar(100), 
    @pPRGDescr varchar(max), 
    @pFCTID int, 
    @pMinCredits int, 
    @pNZQA int, 
    @pNoOccrPerYear int,
    @pFirstMonthOccr int, 
    @pSecondMonthOccr int, 
    @pDOMFee decimal(16,2),
    @pINTLFee decimal(16,2),
    @pIsAval int, 
    @pEmpID int, 
    @pSelCRSInfo as dbo.SelectedCourses READONLY,
    @pPRGID int output  
 as
 begin 

As you can see there is User defined table type SelectedCourses that has the following structure

create type dbo.SelectedCourses
as table
(
    CntrNo int not null IDENTITY(1, 1), 
    CRSID int,
    YearNo int,
    IsCompulsory int,
    EntDT datetime,
    IsAval int,
    EmpID int
);
go

in my stored procedure there is transaction as it has two store fours rows data in defualtCourses table with one row to the ACDPrograms. My store procedure does NOT have try/catch blocks. when iterate through the loop to store four rows the iteration works like a charm but after the last iteraton outside of the loop when I tried to access the @@rowcount, when I call it twice as regular SQL statement (no loops) as

select 'row count' + convert(char(1), @@rowcount) -- This line prints 0 --> INCORRECT
select 'row count' + convert(char(1), @@rowcount) -- this line prints 1 --> CORRECT BUT WHY NOW

first call to it, prints 0 as the value which is no true worst case scenario at the second call it prints the value that it should print in the first which is 1. why it bahves like this why it print two values when it was called twice, shouldn't it print the same value event after million calls to it?

here is the loop part and surrounding of it:

    if @@ROWCOUNT > 0
    begin 
    set @newPRGID = @@IDENTITY;

    -- insert to DefualtCourses table           
    declare @cntr int
    declare @cntr_max int           
    declare @CRSID int
    declare @YearNo int
    declare @IsCompulsory int 
    declare @EntDT datetime
    declare @IsAval int
    declare @EmpID int          

    set @cntr = 1

    select @cntr_max = COUNT(*) from @pSelCRSInfo

    --select 'Max No rows in UDDT' + convert(char(2), @cntr_max)

    while(@cntr <= @cntr_max)
    begin 

    select 
    @CRSID=CRSID,
    @YearNo=YearNo,
    @IsCompulsory=IsCompulsory,
    @EntDT=EntDT,
    @IsAval=IsAval,
    @EmpID=EmpID 
    from @pSelCRSInfo where CntrNo=@cntr

--select @EmpID -- from @pSelCRSInfo-- where CntrNo= @cntr


    insert into DefaultCourses 
    (PRGID,CRSID,YearNo,IsCompulsory,EntDT,IsAval,EmpID)
    values (@newPRGID,@CRSID,@YearNo,@IsCompulsory,@EntDT,@IsAval,@EmpID)               

    select 'rowcount after insert' + CONVERT(char(1), @@rowcount) -- counts all four rows --> CORRECT

    if @@rowcount > 0
    begin 
    select 'counter value' + convert(varchar(12), @cntr) --prints all four iterations --> CORRECT
    set @cntr = @cntr + 1                   
    end
    else
    begin       
    rollback tran                   
    return 3
    end
    end     -- end of loop

    --select 'last counter' + CONVERT(char(2),@cntr) THIS LINE PRINTS 5 --> CORRECT

    select 'row count' + convert(char(1), @@rowcount) -- This line prints 0 --> INCORRECT
    select 'row count' + convert(char(1), @@rowcount) -- this line prints 1 --> CORRECT BUT WHY NOW

    if @@rowcount > 0
    begin 
    select 'im in commit'
    commit tran
    return 1
    end 

    end -- end of the top @@rowcount
    else
    begin 
    --select 'im in rollback'
    rollback tran
    return 3
    end
end -- end of fresh insert block

the values ive used to execute the store procedure

USE [SMSV100]
GO

--delete from ACDPrograms
--delete from DefaultCourses

DECLARE @return_value int,
        @pPRGID int
declare @crs as dbo.SelectedCourses
set @pPRGID = -1

insert into @crs (CRSID,YearNo,IsCompulsory,EntDT,IsAval,EmpID)
values (1, 1, 1, GETDATE(), 1, 1),(2, 1, 1, GETDATE(), 1, 1),(3, 2, 1, GETDATE(), 1, 1),(4, 2, 1, GETDATE(), 1, 1)

EXEC    @return_value = [dbo].[USPSvUpdtACDProgramFromTypes]
        @pPRGCode = N'2323',
        @pPRGName = N'sddsaada',
        @pPRGDescr = N'asdda',
        @pFCTID = 3,
        @pMinCredits = 10,
        @pNZQA = 7,
        @pNoOccrPerYear = 2,
        @pFirstMonthOccr = 2,
        @pSecondMonthOccr = 7,
        @pDOMFee = 4500,
        @pINTLFee = 45000,
        @pIsAval = 1,
        @pEmpID = 1,
        @pSelCRSInfo = @crs,
        @pPRGID = @pPRGID OUTPUT

SELECT  @pPRGID as N'@pPRGID'

SELECT  'Return Value' = @return_value

GO

here is the screen shot of the result window

thanks

Answer 1

You have a long question, but this seems to be the basis of it:

select 'row count' + convert(char(1), @@rowcount) -- This line prints 0 --> INCORRECT
select 'row count' + convert(char(1), @@rowcount) -- this line prints 1 --> CORRECT BUT WHY NOW

@@rowcount refers to the previous select. The first one is converting to a one-character string. That is a bad idea. It will return '*' if the number is too big. But, the query also succeeds, so the second returns @@rowcount.

I would suggest doing something like this:

declare @rc int;
set @rc = @@rowcount;
select 'row count' + convert(varchar(255), @rc)

EDIT:

Your loop need to be something like this:

declare @rc int;

insert into DefaultCourses(PRGID, CRSID, YearNo, IsCompulsory ,EntDT, IsAval, EmpID)
    values (@newPRGID,@CRSID,@YearNo,@IsCompulsory,@EntDT,@IsAval,@EmpID) ;

set @rc = @@rowcount;          

select 'rowcount after insert' + CONVERT(char(1), @rc) -- counts all four rows --> CORRECT

if @rc > 0
begin 
    select 'counter value' + convert(varchar(12), @cntr) --prints all four iterations --> CORRECT
    set @cntr = @cntr + 1                   
end
. . .

That is, copy @@ROWCOUNT into a variable first thing. Then work with the copied value.