SQL Templates in SQL Server Management Studio

August 16th, 2008 | Programming | No Comments

This is one of those nice features that when you stuble upon it you wonder how you ever lived without it.   Just about every DBA, as well as pretty much ever developer, I know has a collection of sql scripts they save some where for reuse to do their job. 

I was looking through menus for an un-related item, and out of curiousity, opened the Template Explorer (or CTRL+ALT+T) and wow, it has been open ever since.

Out of the box for SQL Studio Management Studio 2005 and 2008 it has quite a few nice little templates that are just the simple, SELECT, INSERT, etc but ones that may not be used daily and were you would normal google for the correct syntax or commands to use. 

There is even a dialog to populate the values (Query or right click in the Query Window -> Specify Values for Template Parameters) works just like populating values when you execute a stored procedure.

The beauty though with the Templates is that you can define your own.  Right click on any folder in the Template Explorer and select New.  I was able once to save an existing query window as a Template but trying to repeat the steps, the options are no longer available.  It also had the downside that the Template wouldn’t be visible in the Template Explorer until you restarted SQL Studio Management Studio.  If anyone has gotten this to work for them then please let me know.

Specifying parameters is fairly easy and takes the form:

< Parameter Name , Data Type , Default Value >

My only beef with the Template Explorer is the Search – it uses Windows built-in Indexing Search/Service (what ever it is called) which I have no use for since it doesn’t work.  I really with they just had a simple Filter box on the toolbar of the Template Explorer that would do a full text search of the templates.  Possibly with a few terms to match common names to queries or Template names, such as CTE for Common Table Expressions.

Did I mention it has templates for MDX thought…

Read more

SELECT…BETWEEN, and multiple values

October 11th, 2007 | Programming | No Comments

We are all familar with

SELECT A.* FROM dbo.TableA
WHERE ColumnA BETWEEN ValueA AND ValueB

If not then please see http://msdn2.microsoft.com/en-us/library/bb264565.aspx. 

Which is all fine and dandy if you have one value for ValueA and one value for ValueB.  But what happens say if you have multiple values, say stored in another table.

Cursors?  Shame for even thinking it.  My DBA would rap your knuckles if he even suspected you were thinking about.

A WHILE Loop? Sure.  And to be honest I have used that in the past as well.

But there is a better way with a lot less SQL.


SELECT A.* FROM dbo.TableA A
INNER JOIN dbo.TableB B ON
A.ColumnA BETWEEN B.ColumnY AND B.ColumnZ

Tell me that doesn’t look nice and simple.  So go on, replace those while loops, I can wait…

Read more