How to: Use Parameters in Stored Procedures and User-Defined Functions
Parameters allow you to create flexible SQL routines that use values provided at run time. Because the parameters can be changed each time the procedure runs, you avoid creating one SQL routine for each value that you want to match.
For example, in a Microsoft 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 or user-defined functions you can:
Return a status value to another SQL routine to indicate success or failure and the reason for the failure.
Pass parameter values to other functions or stored procedures.
A parameter takes the place of a constant; it cannot take the place of a table name, column name, or other database object. In Server Explorer, when you run either a stored procedure or user-defined function that contains parameters, you are prompted for the parameter values.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
To view parameters for a stored procedure or user-defined function
In Server Explorer, expand the name of the stored procedure or user-defined function.
Parameters are listed below the name of the SQL routine. In Microsoft SQL Server databases, each parameter name begins with the "@" character. For example, @percentage.
To enter parameter values for a running SQL routine
Run the stored procedure or user-defined function.
If the SQL routine contains one or more parameters, the Run Stored Procedure or Run Function dialog box is displayed.
For each parameter, enter the value to use. Be sure that you provide a value that matches the data type of the parameter.
When you have finished entering parameter values, click OK.
For more information and examples of how your database works with parameters, see the documentation for your database server. If you are using Microsoft SQL Server, see "Parameters" in SQL Server Books Online.