Ask The SQL Server Expert: Questions & Answers

Stored procedure practices in SQL Server

Stored procedure practices in SQL Server

By  Joe Toscano, SQL Server Business Intelligence Consultant / Instructor

SearchSQLServer.com

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

To continue reading for free, register below or login

Requires Membership to View

To gain access to this and all member only content, please provide the following information:

By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

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.