Shortcut: SQL Template Parameters

Everyone has example or template scripts in a folder somewhere that you use for troubleshooting, maintenance, ad hoc queries, or whatever. You open up your script, you replace a few values, and run it. You already saved yourself time.  Maybe you even have those values as variables at the top of your script so it’s super simple to do your replace.

What happens when one of those replacement values can’t easily be parameterized? Maybe it’s a table name, or a database name, or a partial object name, or the datepart parameter of a date function. You can always build dynamic SQL for those things, but that can be a lot more work than it’s worth for what’s supposed to be a quick easy script.

This is where the Template Parameters come in handy. In your template script, insert your parameters in the format of <parameter name, data type, example value>.  The data type & example value portions can be left blank, but cannot be completely omitted. (ie, you can have <Parameter, , > but not <Parameter>).

To use the template, open it in SSMS and use the CTRL+SHIFT+M keyboard shortcut. SSMS will pop-up a prompt window to enter values for your template parameters. Note that SSMS will replace the values literally/directly in your script. It will not add quotes around varchar strings or other similar things. It’s up to you to do that in your template when appropriate.

You can use template parameters as object names, or partial object names. At a previous job, we hosted our client data and named the databases with the product name & client identifier. I built scripts that used template parameters to construct the database name. One quick CTRL+SHIFT+M, and I could have a script with the correct database references in all the right spots.