Wise Owl coding standards for T-SQL (the SQL dialect used in SQL Server)
Part three of a three-part series of blogs

A blog setting out an approach to coding in SQL to ensure consistently high standards across your organisation.

  1. Coding standards for writing SQL in Management Studio
  2. Coding conventions for selecting data in SQL
  3. Coding conventions for programming in SQL (this blog)

Posted by Andy Brown on 01 February 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Coding conventions for programming in SQL

This blog gives coding conventions for more advanced SQL programmers.

Declaring and using variables

Either put a comment before any batch of variable declarations:

-- the minimum date

DECLARE @MinDate DATE = '20001-01'

Or put a comment to the right:

DECLARE @FirstLetter CHAR(1) = 'S'    -- first letter to use

Use PascalCase for variable names, and don't be afraid to use multiple compound words.  The following are good variable names:

DECLARE @EventNameSearchString VARCHAR(100)

DECLARE @EarliestAllowableEventDate DATE

Instead of using PascalCase, you could instead use camelCase.  The difference is in whether you capitalise the first letter.

Stored procedures and functions

Lay out stored procedures/functions like this, commenting each parameter:

CREATE PROC spListEvents(

@MinDate AS DATE                -- the earliest date

, @StartLetter AS CHAR(1)=NULL  -- letter events must start with

)

AS

  

-- list out events beginning with given letter and on

-- or after given date

  

SELECT

e.EventName

, e.EventDate

FROM

tblEvent AS e

WHERE

e.EventDate >= @MinDate

AND (

   

-- if parameter not specified, ignore it

e.EventName LIKE @StartLetter + '%'

OR @StartLetter IS NULL

)

Indenting paired blocks of statements

You should indent any code between two statements which act as bookends to code.  For example:

BEGIN

   

-- do something here

END

Or:

BEGIN TRY

   

-- dodgy thing to try doing

END TRY

BEGIN CATCH

  

-- what to do if went wrong

END CATCH

IF conditions

Indent these, and by preference use BEGIN and END to make them clearer:

-- if today is a Tuesday, say so

IF DATEPART(weekday,GETDATE()) = 3

BEGIN

SELECT 'It''s a Tuesday!'

END

Common Table Expressions

Always indent CTEs, and begin each with a comment explaining what it returns:

WITH RecentEvents AS (

 

-- events since 2000

SELECT

e.EventID

FROM

tblEvent AS e

WHERE

year(e.EventDate) >= 2000

),

EventsStartingWithS AS (

 

-- events starting with S

SELECT

e.EventID

FROM

tblEvent AS e

WHERE

EventName like 'S%'

)

  

-- show events satisfying both conditions

SELECT

e.EventName

, e.EventDate

FROM

RecentEvents AS re

INNER JOIN EventsStartingWithS AS se

ON re.EventID = se.EventID

INNER JOIN tblEvent AS e

ON re.EventID = e.EventID

Subqueries

Indent subqueries and add a comment to explain what they do:

SELECT

c.CountryName

FROM

tblCountry AS c

WHERE (

 

-- number of events for country more than 50

SELECT COUNT(*)

FROM tblEvent AS e

WHERE e.CountryID = c.CountryID

) > 50

For subqueries, you should also put each of the SELECT, FROM, WHERE, etc clauses on a single line, as above, to make it more obvious which is the main query and which the subquery.

Inserting rows, creating tables and creating table variables

For all cases where you are listing the columns in a table, indent them.  For example:

-- create table of events

DECLARE @EventTable TABLE (

EventID INT IDENTITY(1,1) PRIMARY KEY

, EventName VARCHAR(100)

)

Or:

-- add event to table

INSERT INTO tblEvent(

EventName

,EventDate

,EventDetails

) VALUES (

'Blog created'

, GetDate()

, 'Blog on SQL standards published'

)

 

Let me know if I've left some corner of SQL unscathed!

This blog has 0 threads Add post