• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1307
  • Last Modified:

how to determine the records affected in an update sql statement in a stored procdure?

If I have a stored procedure like this:
CREATE PROCEDURE spTest
@UserID int
AS
set NOCOUNT ON
begin

UPDATE pwreset SET UpdateDate = getdate()
             WHERE UserID = @UserID

--get # of records updated (0 or 1)
--and then do something based on that info
--if #recsAffected > 0
--   select 'true' as result
--else
--   select 'false' as result

end
SET NOCOUNT OFF
GO


how would I know how many records were affected?  I guess I could alter the statement a bit and then do some sort of query to find out if the record is found, but if I can just tell if there was a record updated, it'd save a step...

Thanks.
0
davexxxx
Asked:
davexxxx
1 Solution
 
acampomaCommented:
you can set nocount off at the beginningof your statement
or  return @@RowCount
0
 
Scott PletcherSenior DBACommented:
Capture the value of @@ROWCOUNT *immediately after* the UPDATE statement - otherwise the @@ROWCOUNT value gets overwritten by another statement.  For example:

begin

UPDATE pwreset SET UpdateDate = getdate()
             WHERE UserID = @UserID
-- *don't add any statements here!* (or save @@ROWCOUNT in a local var. first)
IF @@ROWCOUNT > 0
   select 'true' as result
else
   select 'false' as result
-- can add statements here if needed

end
SET NOCOUNT OFF
GO

0
 
davexxxxAuthor Commented:
perfect...  @@RowCount was what I was looking for.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now