Mickey's T-SQL Ponderings

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

It’s a Dark and Snowy Day

It’s a dark and snowy day and you get an urgent email. There’s a stored procedure that has been running for 90 minutes. It needs to be fixed STAT. You open up the monstrosity (which hasn’t been cracked open in four years) and it looks like Data’s cat threw up in it. The first thing you want to know, is who wrote the thing? The second thing is how can you possibly make the 400 lines legible quickly? It takes 3 emails and 4 phone calls to track down the authors (plural) of the stored procedure and you are finally able to understand the purpose of the code.

Wouldn’t it have been nice if there had been comments? The author’s (plural) names would have helped as well. Below are three SQL Prompt snippets to help with minimal effort, just a couple of keystrokes and the aid of SQL Prompt.

As for making the code legible, that can be done with 3 little keys. CTRL+K+Y. This short cut will format your code based on the settings you dictate. Everything from indentation of certain parts of the code to the placement of commas.
Before

ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY](@Group AS varchar(1000), @StartDate AS date, @EndDate AS date)AS
SET NOCOUNT ON;
DECLARE @UselessVar AS varchar(max)
SELECT
p.BusinessEntityID ,FirstName,LastName,s.Name AS Store,[Group] AS TerritoryGroup,cr.Name AS CountryRegion,st.Name AS Territory,CustomerCount,s.*
FROM
AdventureWorks2008R2.sales.salesperson AS sp JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID JOIN [AdventureWorks2008R2].[Person].[CountryRegion] AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (SELECT COUNT(*) AS CustomerCount FROM AdventureWorks2008R2.sales.Customer AS c WHERE s.BusinessEntityID = c.StoreID ) AS cc;

But wait, there’s more…

If you also use another 3 key shortcut, CTRL+B+Q, you can alias all the fields, which will also make it easier to read.

Note: If you don’t have the table names aliased, then the entire table name and schema name will be used as the alias.

After

ALTER PROCEDURE [dbo].[SalesTeamAssignment_rptUGLY]
	(
	 @Group AS varchar(1000)
	,@StartDate AS date
	,@EndDate AS date
	)
AS
	SET NOCOUNT ON;
	DECLARE	@UselessVar AS varchar(max)
	SELECT
		p.BusinessEntityID
	   ,p.FirstName
	   ,p.LastName
	   ,s.Name AS Store
	   ,st.[Group] AS TerritoryGroup
	   ,cr.Name AS CountryRegion
	   ,st.Name AS Territory
	   ,cc.CustomerCount
	FROM
		AdventureWorks2008R2.sales.salesperson AS sp
		JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
		JOIN AdventureWorks2008R2.sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
		JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
		JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
		JOIN dbo.fn_Split(',', @Group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
		OUTER APPLY (
					 SELECT
						COUNT(*) AS CustomerCount
					 FROM
						AdventureWorks2008R2.sales.Customer AS c
					 WHERE
						s.BusinessEntityID = c.StoreID
					) AS cc;

Future Love Notes

The first snippet I like to use, I call com. It adds a comment block to the query. I even have this added to my templates for new stored procedures, but I’ll share that one later in the month. I love this header because it utilizing the relatively new placeholders, $NAME$ and $DATE$, which I wrote about on Day 1.

Note: $DATE$ also has the ability to have a custom format. An example would be $DATE(MM/dd/yyyy)$. This would create the date 12/04/2013 for today.

/* ***********************************************************************************
Purpose:    $CURSOR$[Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */

The second snippet is used when you need to modify an existing script. I call it comhist. I personally don’t mind long names, because the intellisence will show me my available options as I type. I usually only need com+<down arrow>+<down arrow>+ tab. This snippet uses  $CURSOR$, $NAME$ and $DATE$. The one downside, is the snippets won’t work inside a comment. (I think I may have to mention that to Red Gate.)

--$DATE$		$USER$			$CURSOR$

The third snippet is used to add a TODO to your code. I use it when I can’t focus on a particular part of the code, but I need a reminder for later. You can then use SQLSearch ( a free tool that can be downloaded from Red Gate) to find all the locations of TODO.

/*TODO: $CURSOR$ - $USER$ - $DATE$ */

Here is what the look like after using them.

/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            [Sample Call of procedure with sample parameter values]
Author:     mstuewe
Date:       12/4/2013

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
12/4/2013		mstuewe			This is a change in the code
************************************************************************************ */

/*TODO: Buy Christmas Presents. - mstuewe - 12/4/2013 */

Note: You can download these snippets from my Script Library under Resources.

Previous Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

Next Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes