A quick search on the internet for examples of SQL will return a multitude of examples, all of which seem to adhere to different conventions for laying out and formatting SQL. I have always advocated coding standards to allow all procedures to follow similar naming, layout and formatting conventions. The main benefit that I have found is that it is easy to understand the procedures.
I was going to document all of my conventions but I found a blog entry by Aaron Bertrand that covers a lot of what I would have said. So instead I am going to only highlight areas where my opinion differs.
A proper and consistent naming scheme
I have been in companies where there has been a good reason to prefix the name of a SP with an identifier of the type of SP it is. I do think that referring to the entity that the SP acts on as the first part of the SP name and the action as the second part makes it easier to understand what a SP does and also easier to find in a list of SPs.
Using parenthesis around parameter list
I do use parenthesis around the parameter list, in fact I am a big fan of parenthesis, especially with the advent of highlighting the opening and closing brackets.
Avoiding datatype/function prefixes on column/parameter names
I prefix all parameters with an “a” – goes back to my coding days. This allows clarity on the values being passed in and as a rule they will not be changed if they are passed in and will only be changed if they are output parameters.
Using column aliases
I prefer the “AS” convention rather than the “=” convention as I only use column aliases when essential for a clear understanding or when a column has been calculated, aggregated or altered in some way.
Use consistent formatting
I would rather scroll horizontally than have code that is not indented correctly (according to my guidelines). Many former colleagues will attest to a certain level of OCD when it comes to the layout of SQL.
;WITH LastFullBackup (
DatabaseName
, MostRecentFullBackupDate
) AS (
SELECT
BS.database_name
, MAX(BS.backup_finish_date)
FROM msdb.dbo.backupset BS WITH(NOLOCK)
WHERE
BS.type = 'D'
AND
CONVERT(DATETIME, BS.backup_start_date, 102) >= (GETDATE() - 7)
GROUP BY
BS.database_name
)
UPDATE @Databases
SET
MostRecentFullBackupDate = cLFB.MostRecentFullBackupDate
FROM @Databases tD
JOIN cLastFullBackup cLFB ON (cLFB.DatabaseName = tD.DatabaseName)
I use commas at the start of the line, parenthesis wherever possible and I indent for clarity. I put joins on a single line although if there are multiple join conditions then I will sometimes put them on different lines but indent them twice. If there are joins that depend on a previous join then I will indent those so that it becomes obvious what the dependencies are.
I use an alias on every table in a query and I qualify the every column reference in the query.
My additional practices
In addition to Aaron’s excellent checklist, I have some to add:
Return value
I always return a value. In most cases it will be one of three values:
-1 – A fatal error has occurred
0 – No error has occurred
1 – No rows have been returned or updated
These basic three values allow other SPs and applications to quickly check the result of a SP. For specific applications I have extended this list of values but only when they are fully documented and everybody involved in the application is aware of the additional values, their meaning and the action to be taken when they occur.
@aSPMode
At times I have a need to change the behaviour of a SP, for example changing the output from summary to verbose. I this situation, I use @sSPMode to determine the mode of behaviour of the SP.
Always have a script
To be able to track changes to any database object, those changes must be scripted and the scripts stored in a version control system. In practice this means that when a SP is created a script should be written that will create the SP and that is repeatable, e.g. it will check for the existence of the SP, drop it and then recreate it and grant appropriate permissions.
/**************************************************
** Initialise Script
**************************************************/
PRINT 'dbo.GetBackupHistory'
SET ANSI_NULLS ON
SET NOCOUNT ON
/**************************************************
** Delete SP
**************************************************/
IF OBJECT_ID('dbo.GetBackupHistory') IS NOT NULL
BEGIN
PRINT CHAR(9) + 'dropping'
DROP PROCEDURE dbo.GetBackupHistory
END
GO
/**************************************************
** Create SP
**************************************************/
CREATE PROCEDURE dbo.GetBackupHistory (
@aSPMode INT = 0
) AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
/**************************************************/
ErrorHandler:
/**************************************************/
PRINT @ErrorDesc
/**************************************************
Return
**************************************************/
RETURN @ReturnValue
GO