ERROR_PROCEDURE does not return a schema name

Now we execute both procedures again:

EXEC Foo.Bar;
EXEC dbo.Bar;
NameCurrentProcID SchemaCurrentProcID NameErrorProcedure SchemaErrorProcedure
Bar Foo Bar dbo
NameCurrentProcID SchemaCurrentProcID NameErrorProcedure SchemaErrorProcedure
Bar dbo Bar dbo

The results meet the expectations. Only the first 2 columns deliver a correct result set consistently for both calls, while the second 2 columns are not really reliable.

It seems to be a bit strange to feed information to SQL Server for the unique identification of an object as we do in case of passing the @@PROCID from the CATCH block of the procedure which caused the error to a common procedure. But even if we would want to identify the procedure in the CATCH block of the procedure itself, we would have to provide this additional information.

It’s been a few years now since SQL Server 2005 was released, so it is fairly likely to assume that the behaviour of ERROR_PROCEDURE() cannot be changed without breaking more or less applications that rely on the current behaviour. It should have been changed ideally before SQL Server 2005 was released or shortly thereafter. Now it is just too late to change the existing implementation. However, it cannot be overly difficult to provide a new function like ERROR_SCHEMA() that returns the schema of the procedure that caused the error. Though this seems to be redundant to me, since SQL Server already provides the OBJECT_SCHEMA_NAME() function that resolves the schema name given an objectid. I guess I would prefer to have a function like ERROR_PROCID() that is equivalent in functionality to @@PROCID and that returns the objectid of the procedure that caused an error. If you have the feeling that this all is a feature that needs to be changed and/or added to SQL Server, you can vote for it in this Connect item. Apparently it is not really high up in the priority list of things to be fixed.

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |