August 25, 2003 at 7:29 am
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
August 25, 2003 at 9:37 am
Should return 0 if there are no records matching Login_Key and Password, 1 if there is a match.
August 25, 2003 at 12:30 pm
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.
August 25, 2003 at 12:37 pm
Well, what does happen? Can you post an example of what the procedure's results are, given certain parameters?
August 25, 2003 at 2:13 pm
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.
August 25, 2003 at 2:15 pm
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)
August 25, 2003 at 3:49 pm
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.
August 26, 2003 at 9:25 am
@@rowcount may be effected by the changes in the trigger code of the table you updated
August 26, 2003 at 2:23 pm
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