Skip Navigation LinksHome > SSW Standards > DeveloperSQLServer > SSW SQL Stored Procedure Formatting Standard

I've been putting together Development Guidelines for my employer and in the process have reviewed many published standards (in the .Net arena) from around the world. In each category, the suggestions at SSW are always among the best. Leon Bambrick -
 

SQL Server Stored Procedure Formatting Standard

This standard outlines the standard on formatting Stored Procedures within SQL Server. Use these standards when creating new Stored Procedures or if you find an older Stored Procedure that doesn't follow these standards within SSW.

Note: Some developers may question the convention for naming stored proc parameters. We agree that this one is certainly open to debate. You need to ask yourself, "how will using this convention help my programmers?" and be prepared to drop the convention if the answer is "well actually it probably won't".

We have always found control naming conventions useful on Access, VB and ASP forms. Therefore we bring them through to SQL Server - you may not. For example there is some question on the usefulness on using a str prefix on string variable types for Char, nChar, Varchar, nVarchar, Text, nText - in this case you really need to know more information than that (ie. the length), however you can at least tell whether you need single quotes in a WHERE clause (ie. WHERE ClientID=' @strClientID '). Overall we believe this convention saves time because the datatypes are intuitive by their name.

Do you agree with them all? Are we missing some? Let us know what you think.

Parameters

  1. Each parameter is to go on a new line.
  2. A prefix of 'p' if the variable is passed in as a parameter i.e. @plngClientID
  3. A three-letter string that indicates the type of variable being used

Parameter Type String Example

Parameter Type
String
Example
We Don't use!
Char, nChar, Varchar, nVarchar, Text, nText str @strEmail varchar(150) Chr chv chrn chvn txt txtn
Datetime, SmallDatetime dte @dteDateCreated datetime dtm dts
Tinyint, Smallint, Integer, Bigint int @intContactID int iny ins inb
Bit bit @bitPrimaryContact bit
Real sng @sngTotal real rea
Float dbl @dblLength float flt
Decimal dec @decLength Decimal(4,2)
SmallMoney, Money cur @curTotal money mny mns
Binary, varBinary bin @binContract binary biv
Image img @imgLogo image
Timestamp tsp @tspCurrent timestamp
UniqueIdentifier guid @guidOrderID Uniqueidentifier
Sql_Variant var @varPrice sql_Variant
Cursor crs @crsInventory Cursor
Table tab @tabMyTable table ( entryid int not null , lname varchar(16) not null )


Example

-- Description Call this proc for every Bounced Email
-- Date        v1 11/11/1999 AC
-- Modified    v2 14/11/1999 DDK
-- Modified    v3 13/12/1999 DDK

CREATE PROC procClientBouncedEmailUpdate

@pstrClientID nvarchar(6),
@pintContactID int,
@pstrBouncedEmail varchar(50)

AS

-- Continued below...

New Lines

  1. Start a new line before each keyword ie. SELECT, FROM, WHERE, ORDER BY.
  2. Each field in a SELECT/UPDATE statement is to go on a new line
  3. Each item is to be indented. (See below)
  4. Place each condition of a WHERE clause on a new line.

Example

UPDATE ClientContact SET DateBouncedLast = getdate(), BouncedTimes = 1, BouncedEmail = @pstrBouncedEmail FROM ClientContact WHERE Email = @pstrBouncedEmail AND ContactID = @pintContactID

RETURN

Figure: Example of bad format

UPDATE ClientContact
SET DateBouncedLast = getdate(),
    BouncedTimes = 1,
    BouncedEmail = @pstrBouncedEmail
FROM ClientContact
WHERE Email = @pstrBouncedEmail
AND ContactID = @pintContactID

RETURN

Figure: Example of good format

Acknowledgments

Adam Cogan