Stored Procedure Parameters

Friday, January 06, 2012

tags SQL

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:

empIDempName
1000John Smith

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook