April 3, 2019 at 2:34 pm
I added error handling to a stored procedure I inherited to check for errors after every update - department policy (and good proctice). Unfortunately, it breaks a piece of SQL which looks at ROWCOUNT. Any advice?
insert table (bla bla
select bla bla bla
IF (@@ERROR <> 0)
BEGIN
SET @ErrMsg = OBJECT_NAME(@@procid)
GOTO ERROR
END
SET @SeqNo = @SeqNo + (CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END) this no longer works
April 3, 2019 at 2:34 pm
forgot to request updates
April 3, 2019 at 2:49 pm
Simpliest way, define two variables (@errcode and @rowcnt for instance) and capture the values of @@ERROR and @@ROWCOUNT immediately following each statement using SELECT @errcode = @@ERROR, @rowcnt = @@ROWCOUNT, and use those variables in your tests and other code.
April 3, 2019 at 4:35 pm
Yep. Save the system variables (@@) into local variables (@) after the relevant statement(s), then test the local variables instead of the system vars. Note that you need to use SELECT rather than SET to capture both, since the SET would/could affect the values of @@ERROR/@@ROWCOUNT. I'd use exactly the same names so it's clearer what the local variables represent, or just make it clear in the actual var name itself:
DECLARE @error int /*from @@ERROR only*/ /*or DECLARE @error_from_@@error int */
DECLARE @rowcount int /*from @@ROWCOUNT only*/ /*or DECLARE @rowcount_from_@@rowcount int */
insert table (bla bla
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
select bla bla bla
...(save @@ROWCOUNT again if that's the rowcount value you want to test)
...
SET @SeqNo = @SeqNo + (CASE WHEN @rowcount > 0 THEN 1 ELSE 0 END)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 3, 2019 at 6:27 pm
Or use TRY-CATCH for error handling.
No need to check @@Error, no need for SET @ErrMsg = OBJECT_NAME(@@procid) (use ERROR_PROCEDURE()), no need for GOTOs
BEGIN TRY
INSERT Whatever ...
....
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE();
ROLLBACK TRANSACTION -- if necessary, but you should probably be using transactions
END CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2019 at 11:35 pm
thanks
instead, I added one more variable @procid and used @rowcount and @err which were already @DECLAREd
then SET @ERR=@ERROR,@ROWCOUNT=@@ROWCOUNT,@PROCID=@@PROCID
and used the variables instead
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply