irregular @@rowcount behavior

  • Hello Everyone,

    Anybody have any experience with strange behavior from @@rowcount. Following Procedure returns 0 or 1 (apparently randomly). Any ideas??

    CREATE PROCEDURE dbo.sp_ChangePassword

    @Login_KEY Int,

    @Password Varchar(12),

    @NewPassword Varchar(12),

    @Updt_UUID Char(36)

    AS

    Declare @Return int

    UPDATE Login

    SET [Password] = @NewPassword,

    Updt_UUID = @Updt_UUID,

    Updt_ts = convert(smalldatetime,getdate())

    WHERE Login_KEY = @Login_KEY

    and [Password] = @Password

    Select @Return = @@ROWCOUNT

    RETURN @Return

  • Should return 0 if there are no records matching Login_Key and Password, 1 if there is a match.

  • That was the intent. I want to tell the user that the record was successfully updated, but it does not seem to happen that way.

  • Well, what does happen? Can you post an example of what the procedure's results are, given certain parameters?

  • If possible, can you do a select from the same table with the same WHERE clause.

    Maybe the parameter values passed in different to what you expect, example funny characters.

    If a sporadic problem insert the rowcount and the parameters passed in into a another TMP table for a trace.

  • When the procedure updates the record correctly, most of the time it returns a 0. My ASP page reports this as an invalid original password and tells the user that the record was not updated even though it was.

    Just wondering if anyone has seen this type of behavior. The BOL indicates that @@rowcount only counts rows in recordsets, but then uses an update statement as an example.

    From BOL:

    Remarks

    This variable is set to 0 by any statement that does not return rows, such as an IF statement.

    Examples

    This example executes UPDATE and uses @@ROWCOUNT to detect if any rows were changed.

    UPDATE authors SET au_lname = 'Jones'

    WHERE au_id = '999-888-7777'

    IF @@ROWCOUNT = 0

    print 'Warning: No rows were updated'

    Here is the version info:

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

  • Question, you have mentioned you are using asp pages, can you post the code how you get SQL to execute the sp. What I am getting at is any middle tier stuff, etc. Your sp seems to be correct maybe there is some problem from SQL to the ASP page(after the sp execution).

    Alternatively run profiler on this object.

  • @@rowcount may be effected by the changes in the trigger code of the table you updated

  • Similar to 'set nocount on' within a stored procedure, I believe there may be a constant you can pass to a recordset or command object that tells it to 'not count' or 'count' the records affected....been awhile since I used ADO from within ASP.

    Also, check to see if your creating your return parameter correctly (if using the command object).

    Hope this helps.

    Edited by - jwiner on 08/26/2003 2:24:58 PM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply