Using Parameters in Stored Procedures

See Also

Parameters enable you to use the same stored procedure to search the database many times, rather than requiring you to create one stored procedure for each value that you want to match. For example, in a SQL Server database, you can add a @HireDate parameter to a stored procedure that searches the employee table for employees whose hire date matches the date you specify. You can then run the stored procedure each time you want to specify a different hire date. Or you can use a combination of parameters to specify a range of dates. For example, you can create two parameters — @BeginningDate and @EndingDate — in the where clause of your stored procedure, and then specify a range of hire dates when you run the procedure.

When you use parameters in stored procedures, you can:

• Return a status value to another stored procedure to indicate success or failure and the reason for the failure.
  
  
• Return parameter values to another stored procedure.

A parameter takes the place of a constant; it cannot take the place of a table name, column name, or other database object.

For more details about how your database works with parameters in stored procedures, see your database documentation.

To view parameters for a stored procedure

• In Data View, expand the name of the stored procedure.

  Parameters for the stored procedure are listed below the stored procedure name. In SQL Server databases, each parameter name begins with the "@" character. For example, @percentage.

When you run a stored procedure that contains a parameter, you specify a parameter value.

To enter parameter values

1. Run the stored procedure. For details, see Running a Stored Procedure.

   If the stored procedure contains one or more parameters, the Run Stored Procedure dialog box is displayed.

2. For each parameter, enter the value to use. Be sure that you provide a value that matches the data type of the parameter.
   
   
3. When you have finished entering parameter values, choose OK.