davexxxx
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
perfect... @@RowCount was what I was looking for.
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