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;