2

this article http://www.novicksoftware.com/tipsandtricks/tips-erorr-handling-in-a-stored-procedure.htm makes this claim about SQL Server development: "errors must be checked after every sql statement of interest". The fairly vague online descriptions of the sql debugger for TSQL neither refute nor support this claim.

So, it it really the case that an "easy to debug" stored procedure is one that has 50% of its code dedicated to detecting errors? Or are there better ways to do it, that could come closer to the sort of ease of finding crashes that we are familiar with in stack trace based debugging in modern programming environments?

Is this an area that calls for creating clever new tools to fill the gaps left by imperfection of the existing programming environment or one that calls for me to get with the program and learn some well known state-of-the-art way of getting this stuff done?

ETA: got it on the try-catch, thanks. In fact, to expand on that, here Recording SQL Server call stack when reporting errors is a discussion of how to emulate stack trace since apparently stack trace is not yet supported by SQL Server. Well, at least that's how it can be emulated in your own codebase written after reviewing the article - a legacy codebase without all this stuff would be harder to deal with.

Community
  • 1
  • 1
EndangeringSpecies
  • 1,564
  • 1
  • 17
  • 39
  • What I do is use the SQL server profiler and watch what SQL queries SPs are executed and then manually run each to see where the problem is. I have seen many tutorials on SQL debuging with integration into VS, but have not been able to get any of them to work right. – Elad Lachmi Mar 28 '11 at 05:58
  • That article was written for SQL Server 2000 which didn't have `try...catch` error handling. – Martin Smith Mar 28 '11 at 11:50

1 Answers1

2

You can use:

ERROR_LINE()

to get information about the line where problem occurred. To use this, you would have to use "try ... catch" for error handling. This information is available in "catch" block.

Documentation : http://msdn.microsoft.com/en-us/library/ms175976.aspx

Examples : http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/

Shamit Verma
  • 3,839
  • 23
  • 22