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

Auto Increment Number in a single UPDATE statement

SQL Server 2005/2008

Suppose in a table, you want to increase the value of a Field (not identity field), you can write a SQL statement as following:

Update yourTable  set  yourField = max(yourField)+1  where .....

But, if the WHERE clause returns more than one records, then what you get is, for that more than one records, all the yourField will be update with the same value. If you want the yourField all has different value, you can write something like this:

declare @max int

select @max= MAX(yourField)  from yourTable

update yourTable set @max=yourField=@max+1 where ........