Recently I found myself having to debug a large stored procedure that calls all sorts of sub-procedures. Also, the data set was rather large and it only failed sometimes. SQL Management Studio in this scenario isn't very helpful for several reasons:

• The number of PRINT messages that it'll show is limited, so if the error is out of range... tough luck.
• Even with PRINT messages it can be hard to pin down which statement is actually in error.
• Line numbers are never accurate.
• It seems View Dendencies sometimes misses out on dependencies.
• View Dependencies doesn't show the number of times a proc is called from another proc.

SQL Profiler, which also comes with SQL Server is a much better tool when it comes to pin-pointing the erronous statement(s), after which you can modify your code to show an error message that prints the values causing the hickup. When you use SQL Profiler, be sure to select more than just the default events. Click Show All Events and select all Error events, as well as the Stored Procedure events that show Starting and Completed of SQL statements with stored procedures. Then, once the root procedure is done, do a Find through the trace for "Error", and you can see exactly which statement is causing the error.