Basic Question - @@ROWCOUNT Returning Zero from Stored Proc SQL S 7

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

I am new to SQL Server & using SQL 7 with Access. The stored proc below
always returns zero in the @IntRecCount parameter. If I substute a number
for @@ROWCOUNT, the proc will correclty return that number.

But for some reason, @@ROWCOUNT always puts a zero into @IntRecCount. Is
there any obvious reason for this? I know the correct records are being
updated in the table - I can see it after running the proc.

Thanks
Mike Thomas


CREATE PROCEDURE _PayMile
@strString varchar(8)=Null,
@intMonth int = Null,
@IntRecCount int = Null OUTPUT,
@RetMsg varchar(50) = Null OUTPUT

AS
DECLARE @Err int

Update mileage set mlgpaid = 1
where (mlgpaid = 0 or mlgpaid is null) and tripdate <= @strString

SELECT @Err = @@ERROR
IF (@Err <> 0) GOTO HandleErr

SELECT @IntRecCount = @@ROWCOUNT
SELECT @RetMsg = " Records Updated"

RETURN

HandleErr:
SELECT @IntRecCount = -1
SELECT @RetMsg = "Error in SP: " + CONVERT(VarChar, @Err)
RETURN
 
Mike
SELECT @IntRecCount = @@ROWCOUNT
Click to expand...

Try

SET @IntRecCount = @@rowcount;

....and put it right after the UPDATE statement. Always use SET when
assigning variables that are not being accessed from a table or view.

Joe
 
MT> SELECT @Err = @@ERROR
MT> IF (@Err <> 0) GOTO HandleErr

MT> SELECT @IntRecCount = @@ROWCOUNT
MT> SELECT @RetMsg = " Records Updated"

@@rowcount represents the result of the preceding select, which is select
@err=@@error

Vadim
 
Back
Top