T-SQL Best Practices - PART I - Coding Style

[center][red] -- This BEGIN/END block assumes a fixed 3-space indent level --[/red][/center]
BEGIN
   DECLARE @ChrPos int, @Err int, @RC int, @Yr int
   DECLARE @SqlCase varchar(8000), 
      @AggFunction varchar(8000), @Delimiter varchar(5)
   SELECT CustomerOrderNo, FirstName, LastName, 
          FullName=LastName+', '+FirstName, OrderDate,
          ProductID, ProductName, Quantity, Price
      FROM Customer C 
         INNER JOIN CustomerOrder CO
            ON C.CustomerID=CO.CustomerID 
         INNER JOIN OrderItem OI 
            ON CO.CustomerOrderID=OI.CustomerOrderID
         INNER JOIN Product P
            ON OI.ProductID=P.ProductID
         INNER JOIN luProductPrice PP
            ON P.ProductID=PP.ProductID and PP.CustomerID=C.CustomerID 
      WHERE Year(OrderDate)=@Yr
      ORDER BY FullName
   SELECT @Err=@@Error, @RC=@@RowCount   
   IF @ChrPos>0 BEGIN
      SET @ChrPos = CharIndex('(CASE ',@SqlCase,@ChrPos)
      SET @SqlCase = Stuff (@SqlCase,@ChrPos+6,0,'WHEN ')
   END
   ELSE BEGIN
      SET @ChrPos = CharIndex(' WHEN ',@SqlCase,@ChrPos+10)
      SET @SqlCase = Stuff (@SqlCase, @ChrPos+1,9,'IS NULL')
   END
   WHILE Len(@AggFunction)<>@ChrPos BEGIN
      SET @ChrPos=Len(@AggFunction)
      SET @AggFunction=Replace(@AggFunction,' (','(')
      SET @AggFunction=Replace(@AggFunction,'( ','(')
   END
END
[green]
/* NOTE:
[ul][li]Each level of indentation is 3-spaces.[/li]
[li]Because the Select portion of the statement requires more than one line to list the columns it is given a double indent and leaves the single indent for the next main clause of a Select statement, namely the From clause. This makes it very clear to the reader that the Select statement is continued and that the next independent line of code is an If statement because the IF is at the same indent level as the SELECT.[/li]
[li]Each Declare statement is devoted to one data type.[/li][/ul]
*/ [/green]
[center][red]-- Another common approach to indentation is --
 the flexible indent.[/red][/center]
BEGIN
   DECLARE @ChrPos      AS int
   DECLARE @Err         AS int
   DECLARE @RC          AS int
   DECLARE @SqlCase     AS varchar(8000)
   DECLARE @AggFunction AS varchar(8000)
   DECLARE @Delimiter   AS varchar(5)
   SELECT CustomerOrderNo, 
          FirstName, 
          LastName, 
          FullName=LastName+', '+FirstName, 
          OrderDate,
          ProductID, 
          ProductName, 
          Quantity, 
          Price
   FROM Customer C 
        INNER JOIN CustomerOrder CO
              ON C.CustomerID=CO.CustomerID 
        INNER JOIN OrderItem OI 
              ON CO.CustomerOrderID=OI.CustomerOrderID
        INNER JOIN Product P
              ON OI.ProductID=P.ProductID
        INNER JOIN luProductPrice PP
              ON P.ProductID=PP.ProductID and 
                 PP.CustomerID=C.CustomerID 
   WHERE Year(OrderDate)=@Yr
   ORDER BY FullName
   SET @Err=@@Error
   IF @ChrPos>0 BEGIN
      SET @ChrPos  = CharIndex('(CASE ',@SqlCase,@ChrPos)
      SET @SqlCase = Stuff (@SqlCase,@ChrPos+6,0,'WHEN ')
   END
   ELSE BEGIN
        SET @ChrPos  = CharIndex(' WHEN ',@SqlCase,@ChrPos+10)
        SET @SqlCase = Stuff (@SqlCase, @ChrPos+1,9,'IS NULL')
   END
END
[green]/* NOTE:[ul][li]Only one variable to a Declare statement.[/li]
[li]Each column within a Select is given it's own line. [/li]
[li]The superfluous [i]AS[/i] has returned.[/li]
[li]The main clauses of the Select statements are at the same level of indentation.[/li]
[li]The columns, the table names and the conditional If statements have their indentation level determined by the preceding line.[/li]
[li]Some writers will employ a rule regarding left justification of repetitive statements as in the Declare statements, the Inner Join clause and the conditional statements within the If statement.[/li][/ul]
*/[/green]