Friday, June 4, 2010

Database Standards Part IV: Stored Procedures

This is Part 4 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.

Stored Procedure
Stored procedures should be required for all create, read, update and delete (CRUD) DML (Data Manipulation Language) statements. Using stored procedures benefits security, consistence, performance and manageability on the DBA side of applications.

Security can be managed by giving GRANT EXECUTE to logins requiring access to DML statements, or in 2005 and greater EXECUTE can be granted to a login on the entire database. Performance gains are realized in compiled query plans and SQL Server’s ability to re-compile only the statement in the SP that needs a new/better plan.

Naming conventions:
  Read - uspPatientGet or GetPatient
  Delete - uspPatientDel or DelPatient
  Report – rptSecurityAudit
  Process – prcProcessPatient

The use of Schemas can better organize the SPs. Do not use prefixes sp, sp_ or xp_. These are used by the SQL Server system or custom extended procedures.

Precompiled SQL can use less memory in the Procedure Cache and require less look up in Proc Cache for existing plans.

Remember not to repeat the schema name in the SP – Patient.GetDetails instead of Patient.GetPatientDetails.

SET ANSI_NULLS ON can be a requirement because it will be depreciated in future SQL Server versions.

SET NOCOUNT ON can be prevent round trips to the client -

Using a Source Control application can reduce comments in a SP. The versioning in TFS or Source Safe gives the developer the ability to associate comments with the check in. Our SPs begin with the following structure

                              WHERE Object_Id = OBJECT_ID(N’[Ptient].[rptPatientReleased] ’)
                                 AND type in (N’P’, N’PC’))
    CREATE PROCEDURE [Ptient].[rptPatientReleased]

ALTER PROCEDURE [Aptient].[rptPatientReleased] AS
    SELECT TOP 10 * FROM NewProcedureView

Always end the SP in GO when creating or altering. The Alter is preferred because it retains permissions on SP. If DROP/CREATE is used, remember to apply the permissions. Return should not be used to return data to the calling application. Return should be reserved for returning the status of the SP. Use INPUT and OUTPUT parameters in the SP.

Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.

Next week I will talk about Triggers and User-Defined Functions.

Thomas LeBlanc


  1. Nice job! There's actually a way to enforce these standards in 2008 and that's via Policy-Based Management. You should check it out, quite a cool/powerful feature.

  2. Will do, thanks for the comments.