Do you have scripts that you need to run frequently, but every time you need to set some different parameters? Are you tired of looking for all of these? Well, Template Variables in SSMS allow you to easily set all of these variables quickly and easily.
A template variable is a special notation within a script. It starts with the less than symbol or “<”, contains 3 comma-delimited parts, and ends with a greater than symbol or “>”. The first part is your name for the variable, the second part is the data type for the variable, and the third part is the optional default value for it.
Let’s look at the following script as an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @<Variable1,sysname,> <Variable1DataType,sysname,>, @<Variable2,sysname,> <Variable2DataType,sysname,>; DECLARE <CursorName,sysname,> CURSOR FAST_FORWARD READ_ONLY FOR /* SELECT statement */ OPEN <CursorName,sysname,>; FETCH NEXT FROM <CursorName,sysname,> INTO @<Variable1,sysname,>, @<Variable2,sysname,>; WHILE @@FETCH_STATUS = 0 BEGIN /* cursor logic */ FETCH NEXT FROM <CursorName,sysname,> INTO @<Variable1,sysname,>, @<Variable2,sysname,>; END CLOSE <CursorName,sysname,>; DEALLOCATE <CursorName,sysname,>; |
This script contains 5 unique template variables: Variable1, Variable1DataType, Variable2, Variable2DataType and CursorName. To quickly and easily replace all of these with your desired values, just select the Query menu item to “Specify Values for Template Parameters” or use the keyboard shortcut Ctrl+Shift+M:
This will open the following window:
In this dialog, just fill in the desired value for each parameter, and press the OK button. All occurrences of that parameter will be replaced with the value that you have specified.
This post is for day seven of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.
You have a little typo on your day seven text at the bottom of the post. Says “day sever”.
Reply
Thanks for catching that Garry. I’ve corrected it.
Reply