Using Parameters

Last modified: June 26, 2023

What is a Parameter?

Parameter is a placeholder for a variable that contains a value of some type that is passed to a database server along with the SQL text at the query execution time. Also parameter can hold values returned by a server after query or stored procedure execution.

You can benefit from using of parameters in the following situations:

• When you execute a query multiple times with different input values
• When you debug a query from your application code

Adding Parameters to a Query Text

There are two types of parameters, that you can add to a query text:

• Named parameter
• Unnamed parameter

Named Parameters

Parameters are declared using : or @ prefix followed by name of the parameter.

For example:

SELECT
  employee.*
FROM
  hr.employee
WHERE
  employee.title = :job AND
  employee.vacation_hours <= :level

:job and :level are parameters in this query.

Unnamed Parameters

Unnamed parameters can be specified as ? symbol.

For example:

SELECT
  employee.*
FROM
  hr.employee
WHERE
  employee.title = ? AND
  employee.vacation_hours <= ?

Parameters for this query are created in the order of appearance.

Modifying Parameter Values and Types

When you run a query that contains parameters with empty values, you will be automatically prompted to initialize them.

To set parameter values and types click Edit Parameters on the Devart - SQL toolbar or main menu. In the Edit Parameters dialog box set parameters’ type, value, and other properties.

Note

When you execute a query with preset parameter values, you will not be automatically prompted to edit them. To edit parameters, press F8 or select Edit Parameters on the Devart - SQL toolbar.