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