Stored Procedure Parameters

One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed unless a default for the parameter is defined or the value is set to equal another parameter.

Specify a parameter name using an @ sign as the first character. Parameters are local to the procedure. The same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects.

Syntax

USE databaseName;
GO
CREATE PROCEDURE procedureName
@parameter1 dataType,
@parameter2 dataType
AS
SELECT columnName FROM tableName;
GO

Example

USE ITGDB;
GO
CREATE PROCEDURE procEmpList
@input1 int
AS
SELECT empID, empName FROM Employees;
GO

Once the stored procedure is created, you can simply execute this procedure by using the following syntax.

EXEC procEmpList 1000

OR

EXEC procEmpList @input1 = 1000

The results will look something like this: