This page has 0 threads | Add post

7,326 reviews

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Coding standards for writing SQL in Management Studio Coding conventions for selecting data in SQL Coding conventions for programming in SQL (this blog)

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.

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

Posted by Andy Brown on 01 February 2017

In this blog

Declaring and using variables
Stored procedures and functions
Indenting paired blocks of statements
IF conditions
Common Table Expressions
Subqueries
Inserting rows, creating tables and creating table variables

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

)

-- 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!

Parts of this blog
Coding standards for writing SQL in Management Studio Coding conventions for selecting data in SQL Coding conventions for programming in SQL (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Coding conventions for programming in SQL

Declaring and using variables

Stored procedures and functions

Indenting paired blocks of statements

IF conditions

Common Table Expressions

Subqueries

Inserting rows, creating tables and creating table variables

Head office

London

Manchester