T-SQL Tricks – Customizing SSMS Templates with Parameters

I wrote briefly about templates in Management Studio (SSMS), and showed the default templates that come with SQL Server. I now want to customize some of the templates in a way that makes sense for me.

If I grab a script I use often, like this one, I can make it generic.

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = 'bob@bob.com'
    GROUP BY
        username
    ,   topic;

I run this often to check things, but I rarely need Bob’s information. Instead, I’ll often get different users, and sometimes I need dates. I can add these changes:

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = '<email, varchar, bob@bob.com>'
    AND startdate > <startdate, datetime, dateadd(m, -1, getdate())> 
    AND enddate <lessthan, char <> <enddate, datetime, getdate()>
    GROUP BY
        username
    ,   topic;

I’ve changed some of my variable items to parameters. I do this by taking an item that I want to make variable, like “bob@bob.com” and changing it to “<email, varchar, bob@bob.com>”.

The format for a template is:

  • name
  • type
  • default

all of which are placed inside angled brackets and separated by commas. Now when I click CTRL+Shift+M, I get this:

templates16

I can click OK for the defaults to be placed in the script, or I can enter new ones. Either way, I save time and effort with saved queries, but saved as templates, not queries I need to edit constantly.

UPDATE: Someone pointed out that the less than, the <, was . I got this from Stack Overflow, which had a good solution. I made the < a parameter as well.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

5 Responses to T-SQL Tricks – Customizing SSMS Templates with Parameters

  1. How do you “excape” the less-than used for the enddate?

    Like

  2. Curt Coker says:

    Thank you! I’ve been using SSMS templates for a long time and somehow avoided learning this cool trick.

    Like

  3. Your 3rd parameter (…AND enddate < '’) , will be replaced by:

    …AND enddate getdate()

    When it should be:

    …AND enddate < getdate()

    How to escape the less-than, so that it won’t parse as a parameter identifier?

    Like

  4. way0utwest says:

    Ah, I see now. I missed that. I’ve updated the code, but basically, I need to make the “<" a parameter.

    Like

Comments are closed.