@@ROWCOUNT & RecordsAffected

[Henry]

I have a stored procedure that runs two update statements within a
transaction and rollsback the transaction if the second update statement
returns an @@ROWCOUNT of 0.

The stored procedure flags records for printing.
The first update statement clears any flags that were set from the previous
printout and the second statement flags the new records for printing, if no
new records are flagged for printing then I want to restore the original
flags from the previous printout.

This stored procedure is being called by an ADP and I would like to know
whether the second update statement updated and rows or now (@@RowCount>0)
If no new records are flagged for printing then I need to know so that I can
decided whether or not to print the report in Access.

Can I simply check the RecordAffected property using:
command.Execute RecordsAffected, Parameters, Options

My concern is that this may report the resulys of the first update statement
in the sp, I am only interested in the results of the second update
statement.

Thanks

Henry

 

[Kenneth Courville]

One method I can think of is specifying an OUTPUT parameter to return the
exact status of your sproc and read that parameter.

i.e.

CREATE spMyprocedure
@status int OUTPUT = 0
AS
-- first trans is run here
IF @@ROWCOUNT < 1
BEGIN
-- first trans failed, return status 1
SELECT @status = 1
END

-- second trans is run here
IF @@ROWCOUNT < 1
BEGIN
-- 2nd trans failed, return status 2
SELECT @status =2
END

 

[Vadim Rapp]

H> My concern is that this may report the resulys of the first update
H> statement in the sp, I am only interested in the results of the
H> second update statement.

In the stored procedure, put SET NOCOUNT ON before the first statement; SET
NOCOUNT OFF before the 2nd statement.


Vadim