Stored procedure practices in SQL Server

How you debug and handle errors in your stored procedures really depends on which version of SQL Server you are working with. SQL 2005 supports structured error handling with TRY … CATCH. Prior version require that you use @@ERROR and @@ROWCOUNT after each critical inser/update or delete. You may consider looking at two tips I wrote: "How to Debug your Transact-SQL Stored Procedures" and "Structure Error Handling in SQL 2005." Fine these and more tips on SQL Server Stored procedures here.

There are several ways to delete duplicate rows from a table. This is the easiest way of de-duplicating a table, and is at its best with fairly small rowsets, and where all the columns in a table are duplicates. We simply run a SELECT DISTINCT on the offending table, storing the results in a temporary table, then delete the contents of the permanent table and re-populate it from the temporary one. While this may be the simplest way, you may have some space issue if you are working with millions of rows. Derived tables and correlated subqueries can also be used.