SQL Server debugging: break on error?

I can't find any debug options when debugging an SP i开发者_Go百科n SSMS on SQL 2008. Is it possible to have it break at the point when an error occurs, so I can examine where exactly the error is thrown and what the values of variables are at that point?

The error I'm trying to debug is:

Msg 8152, Level 16, State 14, Procedure xxx, Line 58 String or binary data would be truncated.

If you set a breakpoint early on you can step through until you find the error.

As a heads-up though, you need to be looking for a case where you're putting a string into a varchar that's longer than the varchar allows.

So, if you have a varcahr(5) and you try and set it to be 'mylongtext' you'll get that error.

To do this in SSMS, right click on the SP and choose EXECUTE TO > New Window. This will create the execute code for you:

DECLARE @RC int
DECLARE @LocationID int
DECLARE @SiteID int

-- TODO: Set parameter values here.

EXECUTE @RC = [MY-DB].[dbo].[P_SELECT_RetrieveToolTips] 
   @LocationID
  ,@SiteID
GO

Move cursor to the EXECUTE statement and press F9 to add a breakpoint.

Then press play (debug). When it stops on the breakpoint, press F11 to step into the SProc and then F10 to step along each line.

You can use Sql Query Analyzer for that. Your message means you are trying to save some value in a field, which length is smaller than the length of that value.