Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure practices in SQL Server

Learn how to debug stored procedures, call a stored procedure from functions and duplicate values in a table with this SQL Server expert's advice.

How can I debug stored procedures and trap errors in a stored procedure? Can you give me an example?

How can I call a stored procedure from functions?

How can I find duplicate values in a table?

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.

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.