2

I am inserting a number or rows into a table using INSERT with SELECT. After the transaction, I want to store both the @@ROWCOUNT and @@ERROR values into locallay declared variables.

INSERT SubscriberList (PublicationId, SubscriberId)
SELECT @PublicationId, S.SubscriberId
FROM Subscribers S

SET @NoRows = @@ROWCOUNT
SET @ErrorCode = @@ERROR

I wasn't sure if this was valid in as much if I call one, will I negate the other?

Neilski
  • 4,385
  • 5
  • 41
  • 74

2 Answers2

8

Set them both at once:

SELECT @NoRows = @@ROWCOUNT, @ErrorCode = @@ERROR

JNK
  • 63,321
  • 15
  • 122
  • 138
4

In addition to @JNK's answer...

I never use @@ERROR now because of TRY/CATCH

BEGIN TRY

    BEGIN TRAN

    INSERT SubscriberList (PublicationId, SubscriberId)
    SELECT @PublicationId, S.SubscriberId
    FROM Subscribers S

    SET @NoRows = @@ROWCOUNT

    ... do more inserts, updates etc

    COMMIT TRAN

END TRY
BEGIN CATCH
    ROLLBACK TRAN
    SET @ErrorCode = ERROR_NUMBER()
    RAISERROR  ...
END CATCH
gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 - I was assuming SQL Server 2000 for some reason though OP doesn't state it. – JNK Aug 25 '11 at 17:17
  • Thank you - I had not seen this construct before, but it looks much better. – Neilski Aug 27 '11 at 08:14
  • @Neilski: You're welcome. For more info on TRY/CATCH, see my answer here please: http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – gbn Aug 27 '11 at 08:16