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 = 'email@example.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, firstname.lastname@example.org>' 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 “email@example.com” and changing it to “<email, varchar, firstname.lastname@example.org>”.
The format for a template is:
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.