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.

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


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 Microsoft on 2/16/2009 at 11:38 AM

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.

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

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() (,

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