Did you find this useful?
Socialize it today.


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

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

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