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.
]]>