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 - http://msdn.microsoft.com/en-us/library/ms189837.aspx

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

IF NOT EXISTS (SELECT * FROM Sys.Objects
                              WHERE Object_Id = OBJECT_ID(N’[Ptient].[rptPatientReleased] ’)
                                 AND type in (N’P’, N’PC’))
  BEGIN
    CREATE PROCEDURE [Ptient].[rptPatientReleased]
      AS RETURN
    GO
  END

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

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