INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

T-SQL Best Practices - PART I - Coding Style by donutman
Posted: 5 Jul 04 (Edited 6 Jul 04)

   This FAQ is a summary of a long thread, Thread183-856683, concerning coding styles that are commonly followed in order to insure that the T-SQL that you write can be easily understood by other programmers.  The suggestions in that thread actually went beyond just questions of style and so I've grouped them into three categories: coding styles, naming conventions and general do's and don'ts.  To see how nicely everything is formatted, it may be necessary to increase the size of your browser window or copy and paste the code into a fictitious stored procedure in EM in order to see it in full color!

Coding Style
  • Adopt a convention and enforce it company wide.
  • Use indenting to help break up the sequence of statements, so that conditional statement flow is readily apparent.
  • Some people like to use a fixed 3- or 4-spaces per indent level, while others like it to vary depending on the statement preceding the indent.
  • Capatilize the entire keyword that EM would display in blue (e.g. within a stored procedure).
  • Camel case the names of system functions like CharIndex() that EM would display in fuscia.
  • Keep lines short so that horizontal scrolling isn't necessary.
  • Use Begin and End blocks. Place the Begin at the end of the conditional clause and place the End on its own line but at the same indent level as the If or While that initiated its use.
  • Use SET for assignment statements. An exception is SELECT @Err=@@Error, @RC=@@RowCount when both assignments must be made at the same time.
  • Use SELECT for assignment statements that require a query.
  • Dispense with the superfluous AS in data type statements and when aliasing.

CODE

-- This BEGIN/END block assumes a fixed 3-space indent level --
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

/* NOTE:
  • Each level of indentation is 3-spaces.
  • 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.
  • Each Declare statement is devoted to one data type.
*/

-- Another common approach to indentation is --
 the flexible indent.
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
/* NOTE:
  • Only one variable to a Declare statement.
  • Each column within a Select is given it's own line.
  • The superfluous AS has returned.
  • The main clauses of the Select statements are at the same level of indentation.
  • The columns, the table names and the conditional If statements have their indentation level determined by the preceding line.
  • 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.
*/
   I believe that both approaches and pretty much any combination of the two will produce clean, readable code.  The first approach conserves vertical space, but the second one makes it easier to see a list of items like the column names and variable names.  

   Many thanks to those who contributed to the original thread that helped make this FAQ and the next two: Part II - Naming Conventions and Part III - Do's and Don'ts:
                 ESquared              MDXer              SQLSister
                 JayKusch               nigelrivett         TJRTech
                 john76                  SQLBill              vongrunt


-Karl (donutman)

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close