error_line and error_procedure does not give accurate information when error occurs in a UDF - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 412136 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/6/2009 2:07:26 PM
Access Restriction Public

Description

When a UDF is invoked through a SELECT or other statement and error occurs, and you 
trap this error in a CATCH block, error_line and error_procedure relates to the batch that fired the statement, not to the UDF. This makes troubleshooting more difficult.

When you execute a UDF through EXEC, error_line and error_procedure are accurate.

Obviously, I don't expect this behaviour for inline functions, only multi-statement functions.

I reckon that the current behaviour may be "by design", why it would have been more appropriate to file this as a suggestion. In any case, I don't really expect this change in SQL 2008.
Sign in to post a comment.
Posted by Adam [MSFT] on 2/16/2009 at 11:38 AM
Erland,

Thanks for pointing this inconsistency out.

We have an improvement scheduled to cleanup our error handling story in SQL 11. I will create a work item for this improvement to investigate what the proper behaviour is here (I agree with you - the current design is both inconsistent and not as informative as it could be). This type of fix doesn't meet the bar for a Katmai service pack.

-Adam
SQL Engine Development
Posted by Steve Kass on 2/8/2009 at 11:14 AM
Actually, perhaps the bug is that you get the "correct" line number and routine when using EXEC... (but let's hope they don't change that and provide less information. Again, this is a good suggestion, if it's not considered a bug).
Posted by Steve Kass on 2/8/2009 at 11:12 AM
Erland,

This is the documented behavior for error_procedure at least, but I agree that a design change would be helpful. According to the BOL article for error_procedure() (http://msdn.microsoft.com/en-us/library/ms188398.aspx),

"Returns NULL if the error did not occur within a stored procedure or trigger."

SK