如何找出发生错误的存储过程的哪一行？

当我尝试在MS SQL 2005数据库上运行特定的存储过程时,出现如下错误:

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

查询中的SP很长并且调用其他SP.这个错误显然是由SQL本身产生的,并且一直向上返回调用堆栈,但没有提到哪个SP或行号导致了问题.如何找出错误抛出的位置,以便我可以更轻松地调试它？

使用Try/Catch块可以为您提供所需的内容.

在CATCH块的范围内,可以使用以下系统函数来获取有关导致执行CATCH块的错误的信息:

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

那么,在你的情况下,ERROR_LINE()和ERROR_PROCEDURE()应该是你想要的......