How do I find out which line of which stored procedure an error occurred on?

Question

When I try to run a particular stored procedure on my MS SQL 2005 database, I get an error like the following:

Subquery returned more than 1 value. This is not permitted when
the subquery follows =, !=, <, <= , >, >= or when the subquery
is used as an expression

The SP in query is very long and calls other SPs. This error is obviously being produced by SQL itself, and returned all the way up the call stack, but without mentioning which SP or line number caused the problem. How can I find out from where the error was thrown so I can debug it more easily?

Answer 1

Using the Try/Catch block should give you what you are looking for.

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

* ERROR_NUMBER() returns the number of the error.
* ERROR_SEVERITY() returns the severity.
* ERROR_STATE() returns the error state number.
* ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
* ERROR_LINE() returns the line number inside the routine that caused the error.
* ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

So, in your case, ERROR_LINE() and ERROR_PROCEDURE() should be what you want...