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:

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.

Filed under: Blog Tagged: syndicated, T-SQL