In SQL Server 2005 we can use the TRY ---END TRY as well as CATCH---END CATCH. Its always better to use this inside the Database objects like Stored Procedures or Cursors.

By Jolly kid

It depends. If you're running 2005 or better, you could be writing your stored procedure in a native .Net language. In that case your error handling would be the exception handling mechanism of that language. If your stored proc is in T-SQL, the method would be to check the "@@ERROR global/function after any line that may cause an error: if @@ERROR = 0 begin commit; -- OK to proceed end else begin rollback; -- or whatever end