Tuesday, November 18, 2008

Stored Procedure, Trigger, and UDF Formatting Preferences

This is part 2 of my personal standards and, when I have the power, the standards at my office. Part 1 is Naming Conventions, you may want to take a look at that before reading this post.

I have to admit to being very finicky about formatting not only in SQL, but any programming language. I am a huge proponent of white space and consistency. I also always wish I commented more thoroughly.

Some basic rules:
  1. Set NoCount On at the top
  2. Declare any variables at the top of the procedure
  3. Create any temporary tables immediately after the variable declarations
  4. Set any variable defaults after temp table creation.
  5. Capitalize first letter of Keywords. I know alot of people like all caps for keywords, but I am spoiled by the tools that colorize them.
  6. Always use Begin and End in control of flow blocks (If-Else, While, etc...)
  7. Whitespace between statements.
  8. Use "As" when aliasing coumn names. first_name + ' ' + last_name As full_name.
  9. When using "Between" I do not capitalize the "and". amount Between 10 and 20. I do this to differentiate the "Between and" and the "And" for adding criteria. amount Between 10 and 20 And product_id = 10.
  10. Table aliases should be meaningful and in CAPS.
  11. Whitespace between operands. amount = 10.
  12. Indenting
    • Column lists - between Select and From, in Group By, in Order By, and Having
    • Tables in From
    • Criteria in On
    • Criteria in Where
    • Begin and End inside Control of Flow statements, and the inside the Begin and End
  13. Always end Procedure or Trigger with a Return.
Here is a basic example:

CREATE/ALTER PROCEDURE SCHEMA.proc_name
(
-- required paramters (no defaults - first)
@parameter datatype [= default],
-- output parameters always last in the list
@output_parameter datatype output
)
AS

/* Author Name
**
** Purpose - this procedure is used to show formatting
**
** Example
** ----------
** Declare @output_parameters datatypes, @retval Int -- if needed

Exec @retval = schema.proc_name parameter list

Select @retval As return_value, @output_parameters As paramter_names
**
** History
** ----------
** mm-dd-yyyy - Created
** mm-dd-yyyy - Initials - change made and why
**
*/

SET NOCOUNT ON

DECLARE
@variable datatype,
@variable2 datatype

CREATE TABLE #temp
(
column1 datatype,
column2 datatype
)

SET @variable = value

IF @parameter = value
BEGIN
SET
@variable2 = value
END
ELSE
BEGIN
SET
@variable2 = value2
END

INSERT INTO
#temp
(
column1,
column2
)
SELECT
column1,
column2
FROM
table1 T1 JOIN
table2 T2 ON
T1.primary_key = T2.foreign_key
WHERE
T.column1 = @variable AND
T2.column2 = @variable2

SELECT
column1,
column2
FROM
#temp

RETURN
For an example of my rules and recommendations for triggers please see my article, Introduction to DML Triggers , on SQLServerCentral

3 comments:

  1. Jack, I'm with you on the formatting thing - I end up reformatting a lot of inherited SQL for readability and consistency purposes. I wonder if any of those online SQL formatting tools could be modified to enforce standards such as these....

    ReplyDelete
  2. Tim, I don't know if any of the online tools can be configured for standards. I have not yet used, but will be soon using, SQLRefactor by RedGate which allows you to define the formatting standards and does the formatting for you.

    ReplyDelete
  3. I've tried ApexSQL's Refactor [http://www.apexsql.com/downloads_addins.asp#refactor] which integrates with SSMS but I wasn't able to customize it enough. I've been using the online version of SQLinForm [http://www.sqlinform.com/] and I've been pretty happy with the results. Haven't tried the RedGate tools yet, though.

    ReplyDelete

So what do you think I am?