Use query parameters

You can use parameters in SQL queries to pass values to the database server at runtime instead of hard-coding them in the statement.

What is a parameter?

A parameter is a placeholder for a variable that stores a value of a specific data type. The parameter value is passed to the database server together with the SQL statement at execution time. Parameters can also store values returned by the server after executing a query or stored procedure.

Parameters are useful in scenarios such as:

• Running the same query multiple times with different input values.
• Debugging a query directly from application code.

Add parameters to a query

Parameters are declared by using the @ prefix followed by name of the parameter. The prefix is part of the parameter name.

For example:

SELECT
  Employee.*
FROM
  HumanResources.Employee
WHERE
  Employee.Title = @Job AND
  Employee.VacationHours <= @Level

In this example, @Job and @Level are parameters.

Modify parameter values and types

If you run a query that contains parameters without assigned values, the Edit Parameters dialog automatically opens so you can initialize them.

To manually set parameter values and types:

1. On the SQL toolbar, select Edit Parameters, or press F8.
2. In the Edit Parameters dialog, specify the parameter type, value, and other properties.

Note

If you execute a query with preset parameter values, the Edit Parameters dialog does not open automatically. To change parameter values, press F8 or select Edit Parameters on the SQL toolbar.