Noob question on an if..then statement
-
I'm having a problem with an if..then statement that I don't understand and can't fix. Below is the snippet of code. I have two traces open. One trace filtered on %%::ERROR::%%, and the other filtered on '%Send_Get_MD_Message - Error%'.
In the trace filtered for %%::ERROR::%%, I can see entries for '::ERROR::BEFORE', '::ERROR::AFTER' & '::ERROR::NOERROR', but not '::ERROR::DURING', ever.
PRINT '::ERROR::BEFORE'
SELECT @ErrorNumber = ISNULL(ERROR_NUMBER(), 0)
IF @ErrorNumber != 0
BEGIN
PRINT '::ERROR::DURING'
SET @lMessageDetail = ''
SET @lMessageDetail = + ' SQL Error number=' + ISNULL(CONVERT(VARCHAR, @ErrorNumber), 'Null')
SET @lMessageDetail = @lMessageDetail + ' SQL Error Description=' + ISNULL(ERROR_MESSAGE(), 'Null')
EXECUTE master..xpLog_Info 10, 'Send_Get_MD_Message - Error', @lMessageDetail
END
ELSE
BEGIN
PRINT '::ERROR::NOERROR'
END
PRINT '::ERROR::AFTER'
In the trace filtered for '%Send_Get_MD_Message - Error%', it's firing nearly every iteration. This is the only place in the code where this text is present, so I know it's this location. But the '::ERROR::DURING' never appears in the trace, it's almost like the extended proc is firing regardless of the value of @ErrorNumber.
Can anyone help explain this?
-
From Books Online
ERROR_NUMBER ( )
When called in a CATCH block, returns the error number of the error message that caused the CATCH block to be run.
Returns NULL if called outside the scope of a CATCH block.
So unless that fragment of code is from a catch block, Error_Number will always be null. Use @@Error instead if you're trying to look at error numbers outside of a try ... catch construct
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks,
If I make a change and capture the @@ERROR after every SQL statement in the proc, with something like:
IF (@@ERROR <> 0) AND (@ErrorNumber = 0)
BEGIN
SET @ErrorNumber = @@ERROR
END
I still don't have an error code. The sequence in my trace still looks like:
::ERROR::BEFORE
::ERROR::NOERROR
::ERROR::AFTER
The ::ERROR::DURING is never written to the trace but the xpLog_Info is still firing and
IF @ErrorNumber != 0still evaluates to 0. -
sqlpadawan_1 (3/28/2012)
Thanks,If I make a change and capture the @@ERROR after every SQL statement in the proc, with something like:
IF (@@ERROR <> 0) AND (@ErrorNumber = 0)
BEGIN
SET @ErrorNumber = @@ERROR
END
Common mistake there...
@@error records the error number of the last statement, not of the last error that occurred. So your IF checks @@error, if it's not 0 then the SET allocated the error code of that IF statement (which is likely 0) to @ErrorNumber.
You have to check @@error after every single statement and if you want to do something with it, the first thing you do is put it into a variable, then check and assign and do whatever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok, so if I understand your last post, I would need something like this after every sql command
SET @ErrorNumber = @@ERROR
IF (@ErrorNumber != 0)
BEGIN
SET @RecordErrorNumber = @ErrorNumber
END
And then check the @RecordErrorNumber
PRINT '::ERROR::BEFORE'
IF (@RecordErrorNumber != 0)
BEGIN
PRINT '::ERROR::DURING'
SET @lMessageDetail = ''
SET @lMessageDetail = + ' SQL Error number=' + ISNULL(CONVERT(VARCHAR, @RecordErrorNumber), 'Null')
EXECUTE master..xpLog_Info 10, 'Send_Get_MD_Message - Error', @lMessageDetail
END
ELSE
BEGIN
PRINT '::ERROR::NOERROR'
END
PRINT '::ERROR::AFTER'
-
Yup, that's far more likely to work.
http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for all the help Gail. The problem comes down to the extended proc. No matter the value of the @RecordErrorNumber, that proc fires inside that if loop, and nothing else does (no other procs, print statements, etc...). I'm not sure how to proceed at this point.
Thanks,
Kevin
-
Is this a custom extended stored procedure: xpLog_Info?
-
Yes, it's custom.
-
Can you run this as part of whatever code you're testing and post exactly what it prints out?
PRINT '::ERROR::BEFORE'Print 'Error number = ' + @RecordErrorNumber
IF (@RecordErrorNumber != 0)
BEGIN
PRINT '::ERROR::DURING'
SET @lMessageDetail = ''
SET @lMessageDetail = + ' SQL Error number=' + ISNULL(CONVERT(VARCHAR, @RecordErrorNumber), 'Null')
print @lMessageDetail
EXECUTE master..xpLog_Info 10, 'Send_Get_MD_Message - Error', @lMessageDetail
END
ELSE
BEGIN
PRINT '::ERROR::NOERROR'
END
PRINT '::ERROR::AFTER'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Below is the code that I used, there is a simulator running against the database and I had to filter down on some of the print messages:
PRINT '::ERROR::BEFORE'
Print 'SQL Error number= ' + ISNULL(CONVERT(VARCHAR, @RecordErrorNumber), 'Null')
IF (@RecordErrorNumber <> 0)
BEGIN
PRINT '::ERROR::DURING'
SET @lMessageDetail = ' SQL Error number=' + ISNULL(CONVERT(VARCHAR, @RecordErrorNumber), 'Null')
PRINT '::ERROR::DURING' + ISNULL(@lMessageDetail, '')
EXECUTE master..xpLog_Info 10,'Send_Get_MD_Message - Error',@lMessageDetail
END
ELSE
BEGIN
PRINT '::ERROR::NOERROR'
END
PRINT '::ERROR::AFTER'
I filtered down the trace on this:
Continued filter:
And the results look like this (I filtered out all of the usual columns that were either blank or zero):
-
It looks to me like it's working as intended.
Bear in mind you have multiple sessions mixed up there, but if you look at each session on its own, it's either printing 'no error', or it's
setting the error message statement then running the xp.
The only thing I don't see that I'd expect is the print of error during.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Maybe I'm reading this wrong then. Looking at the middle session, spid 67, it executed the xpLog_Info proc and also printed the ::ERROR::NOERROR line. I guess I would have thought the IF loop would only execute one or the other. I agree about printing the error inside the if loop. I've tried to print the error code from there or even insert it into a table, and nothing ever appears, that's what makes me believe I'm not getting an error (event though reading the logic I know I am).
-
67 did the following, in sequence:
Assign message
execute xp
Assign message
execute xp
print before
print error number
print no error
print after
Looks like 3 separate executions, 2 with errors, one without.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for your help with this Gail. I guess I need to dig deeper and see what is causing the errors.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply