SQL Server: Query Template, a Time Saving Feature

While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time saving facility. You can save your script as template, like following is a simple query script, which I like to execute for different databases and with different order by clause.

USE AdventureWorks

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName, 

c.MiddleName, c.LastName, c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e 

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st 

ON st.TerritoryID = s.TerritoryID 

INNER JOIN

Person.Contact AS c 

ON c.ContactID = e.ContactID 

ORDER BY c.FirstName

One solution to avoid any labor work to change above script manually according to my requirements, is to create a dynamic query. But, in SQL Server 2008 I would prefer Query Template, by saving above query in following Template.

USE 

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName, 

c.MiddleName, c.LastName,

c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e 

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st 

ON st.TerritoryID = s.TerritoryID 

INNER JOIN

Person.Contact AS c 

ON c.ContactID = e.ContactID 

ORDER BY 

Next time when I need to execute this script, I will just open the template and press Ctlr+Shift+M or click on replace button, and it will ask me  for template parameters value and on pressing OK button it will replace template parameters with my values on whole script.

Defining a template needs three parameters separated with comma and enclosed with < and > signs.

<TemplateParameter, ParameterDataType, DefaultValue>

Replace Button