How to debug in SQL 2008

I know there are some nice new features in SQL 2008, a few days ago, finally I got it all tried out and succeeded.

1. Set breakpoint in your Stored Procedure

This feature is only available in SQL 2008, not SQL 2005, and you need Debugger permission to do so. You can conveniently click the left side grey column to set the breakpoint in your stored procedure.

Then, open a new query window, write a statement to execute your stored procedure, provide parameters if necessary.

exec dbo.yourSpName @para1='value1', @para2='value2' .....

highlight the above statement, and click the debug icon next to Execute command at the toolbar, press F11 to step into the body of your stored procedure, also F10 to run the statement, you can check the values of all the variables in the context.

2. Add try-catch block

This is also new in SQL 2008, sample code is as following:

Log the error messages in your logTable, so you can examine them later to see what happened.

declare @ErrorMessage varchar(MAX) begin try            <your sql statements here ....> end try begin catch   select @ErrorMessage = ERROR_MESSAGE()   EXEC [dbo].[log_Error]        @process_name = 'your process name',               @user_message = 'Error occurred …',        @system_message = @ErrorMessage end catch