Can a Stored Proc return the number of records affected? 1

[ousoonerjoe]

I have a procedure that makes several updates/inserts based upon the data passed to it. There are no special Return Values/RecSets as it is now. The question i have is, Can a Stored Proc return the number of affected records?

For example:

CREATE PROCEDURE usp_Do_Something(@StartDate DATETIME)
AS

If @StartDate...
    UPDATE Table1...
    INSERT Table2...
ELSE
    INSERT Table1...
    INSERT Table2...
    UPDATE Table3...
END
INSERT Table4...
UPDATE Table5...

RETURN [b][i]Number of records affected[/i][/b]

Is this possible?

As always, any tips, comments, and/or suggestions are welcome. Thank you

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[RobertT687]

Yes.
You can try something like this:
CREATE PROCEDURE usp_Do_Something(@StartDate DATETIME, @RecsEffected INTEGER OUTPUT)
AS
SET @RecsEffected = 0
If @StartDate...
UPDATE Table1...
SET @RecsEffected = @RecsEffected + @@RowCount
INSERT Table2...
SET @RecsEffected = @RecsEffected + @@RowCount
ELSE
INSERT Table1...
SET @RecsEffected = @RecsEffected + @@RowCount
INSERT Table2...
SET @RecsEffected = @RecsEffected + @@RowCount
UPDATE Table3...
SET @RecsEffected = @RecsEffected + @@RowCount
END
INSERT Table4...
SET @RecsEffected = @RecsEffected + @@RowCount
UPDATE Table5...
SET @RecsEffected = @RecsEffected + @@RowCount

RETURN

Untested.

Your calling code will need to be able to read the value returned in @RecsEffected.

 

[ousoonerjoe]

Thanks. I forgot all about @@ROWCOUNT.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.