Specify values for template parameters sql

The direction of a parameter is either input, a value is passed into the body of the procedure, or output, the procedure returns a value to the calling program. The default is an input parameter. The procedure returns the current value of the output parameter to the calling program when the procedure exits. The calling program must also use the OUTPUT keyword when executing the procedure to save the parameter's value in a variable that can be used in the calling program.

The following example creates the Production. The parameter name and variable name do not have to match. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. Any additional feedback? Note If the default value is a character string that contains embedded blanks or punctuation, or if it starts with a number for example, 6xxx , it must be enclosed in single, straight quotation marks.

Submit and view feedback for This product This page. Viewed 3k times. Improve this question. Jon Seigel Cool feature, way underrated imho.

Add a comment. Active Oldest Votes. Improve this answer. Caleb Hattingh Caleb Hattingh 8, 2 2 gold badges 28 28 silver badges 43 43 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook.

Sign up using Email and Password. Post as a guest Name. You only need to specify the value for a parameter once, and SSMS automatically replaces the parameter variables with the value specified.

This way, we do not need to go through each line of code, look for the variable, and assign value. It also saves time and eliminates human errors. You can run the script directly, and it creates a database for you. You specified only a few parameters values, and your script is ready. Launch this template and view the query with variables.

In a stored procedure, we define the variable and its data types such as int, varchar , char, float. In the below template, we see that it asks for data types of both variables param1 and param2. Now, launch the specify values for template parameters, as shown below.

Here, we see that it defines variable data types. By default, it uses int data type. We can change the data type as per our requirement. It is a text box, and you can write the data type. It should be a valid data type as per the SQL Server version. Click OK, and you can see changes in the script. SSMS allows you to define your script templates.

It is an excellent way to define your scripts and frequently used scripts such as procedure, function, views. For this demo, I am using the alter stored procedure template code and saving it as a new custom template. Suppose you want to create a template to alter a stored procedure.