Proper T-SQL Stored Procedure Formatting

Here is a very good blog post from Aaron Bertrand (another SQL MVP)  on how to write and format stored procedures. As the developers I work with can attest, I am pretty picky about minor details when it comes to T-SQL in stored procedures. Sometimes these seemingly minor details can have a significant effect on performance, while in some cases it is more of a maintainability issue.

Here are a couple of examples I have seen recently.  In the good example #1 below, notice the proper indentation and whitespace, SET NOCOUNT ON, the brackets around T-SQL reserved words that are used for column and table names, and the semicolon statement terminators.

-- Bad example #1 
 ALTER PROCEDURE [dbo].[GetAllGroups] 
 AS 
  
 SELECT  GroupID, 
 Sid, 
 GroupName, 
 CreateDate, 
 Description, 
 Active 
 FROM    [Group] 
 ORDER BY GroupName 
  
 RETURN
-- Good Example #1
 ALTER PROCEDURE [dbo].[GetAllGroups] 
 AS
 
      SET NOCOUNT ON; 
  
      SELECT  GroupID, [Sid], GroupName, CreateDate, [Description], Active 
      FROM dbo.[Group] 
      ORDER BY GroupName; 
        
      RETURN;

 

In the good example #2 below, notice the proper indentation and whitespace, the LEFT OUTER JOIN, the use of AS for a table alias, the upper case for all T-SQL reserved words, and the semicolon statement terminators.

-- Bad Example #2 
 ALTER PROCEDURE [dbo].[GetFeedTags] 
 ( 
 @FeedID int, 
 @Count int = 15, 
 @UserID int, 
 @Filter nvarchar(255) = null 
 ) 
 AS 
  
 SET NOCOUNT ON 
 DECLARE @OneBit bit 
 SET @OneBit = 1 
  
 select top (@Count) 
  
 t.Category, 
 COUNT(t.Category)  AS TagCount 
  
 from dbo.PostCategory t  with(nolock) 
  
 LEFT JOIN dbo.SubscriptionRead sr with(nolock) ON t.PostID = sr.PostID AND sr.UserID = @UserID 
  
 WHERE t.IsPublic = @OneBit AND t.FeedID=@FeedID and ((@Filter IS NULL) OR (t.Category 
 LIKE @Filter + '%')) 
 AND sr.PostID IS NULL 
  
 GROUP BY t.Category 
 ORDER BY TagCount DESC, t.Category ASC

-- Good Example #2 
 ALTER PROCEDURE [dbo].[GetFeedTags] 
 ( 
       @FeedID int, 
       @Count int = 15, 
       @UserID int, 
       @Filter nvarchar(255) = NULL 
 ) 
 AS 
  
      SET NOCOUNT ON;
       
      DECLARE @OneBit bit; 
      SET @OneBit = 1; 
  
      SELECT TOP(@Count) t.Category, COUNT(t.Category) AS [TagCount] 
      FROM dbo.PostCategory AS t WITH(NOLOCK) 
      LEFT OUTER JOIN dbo.SubscriptionRead AS sr WITH(NOLOCK)  
      ON t.PostID = sr.PostID AND sr.UserID = @UserID 
      WHERE t.IsPublic = @OneBit 
      AND t.FeedID = @FeedID AND ((@Filter IS NULL) OR (t.Category LIKE @Filter + '%')) 
      AND sr.PostID IS NULL  
      GROUP BY t.Category 
      ORDER BY TagCount DESC, t.Category ASC;
      
      RETURN;

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

6 Responses to Proper T-SQL Stored Procedure Formatting

  1. Mohammad Reza says:

    Tnx …this post helped me a lot.

  2. Mahmoud says:

    thanks :)was helpful for me

  3. Paul says:

    I also like to keep my DML sub-clauses (such as ON & AND above) indented, as it breaks the query into clause-sized chunks.

  4. Paul says:

    I personally use Red Gate\’s SQL Prompt which reformats other ppl\’s nastily unreadable code into exactly the format I like. There are also a few cheaper/free alternatives out there which work just fine.

  5. V says:

    What about a BEGIN/END block for the whole SP? Semicolons are really only useful for being explicit about where a comman ends. But for the same reason, every SP should have a BEGIN/END wrapper to make explicit what\’s in the SP and what\’s not. I\’ve had developers write test SQL immediately following the SP, and guess what – that line of code will accidentally become part of the SP. Not so if you are EXPLICIT about the block of code that comprises the SP.

  6. Tom says:

    Finally, someone is advocating the use of the keyword "AS" to specify table aliases. I am the only one in my company who does this, and I feel that this plays a big role in making complex queries easier to understand.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s