>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
Advertisements

Posted on May 18, 2011, in Sql Server 2008, SSMS, TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

  1. >a Wowww tips 🙂 I used to store my queries in a file called query.txt for further uses , this seems to me a nice alternative , thank you.Javin 10 tips on using find command in Unix

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

%d bloggers like this: