Some tips on Stored Proc

Here are some tips while using Stored Procs by Christa Carpentiere, publushed in March 2004 issue.

Use SET NOCOUNT ON
Stored procedures by default will return the count of rows affected by each statement in the procedure. If you don't need to use this information in your application (most applications don't), use the SET NOCOUNT ON statement in your stored procedure to stop this behavior.

Don't Use the sp_ prefix
The sp_ prefix is reserved for system stored procedures. The database engine will always look for stored procedures with this prefix first in the master database. This means that it will take just a bit longer to finish processing while the engine looks through the master database first, and then the database where the stored procedure actually resides.

Use OUTPUT Parameters Where Possible
You can add a little speed and save a little processing power by using an OUTPUT parameter to return scalar data. Where your application needs a single value returned, try this approach instead of materializing a result set.

Provide a RETURN Value
Use the return value of a stored procedure to return processing status information to the calling application. This will make it much easier to handle errors in your calling application and provide useful information about problems to your end users.

DDL First, DML Later
SQL Server will recompile a stored procedure when a DML statement is executed after a data definition language (DDL) statement, where the DML references any object modified by the DDL. This happens because SQL Server needs to take into account the changes made to the object by the DDL in order to create a plan for the DML. If you take care of all your DDL at the start of the stored procedure, then it only has to recompile once. If you mix DDL and DML statements, you will force the stored procedure to recompile multiple times, and that will adversely affect your performance.