Getting Started with Stored Procedure

Writing the first stored procedure

Here is the first stored procedure source code

CREATE PROCEDURE Delete_Employee
   (@EmployeeId INT)
AS

BEGIN
   DELETE FROM  Employees
   WHERE  EmployeeId = @EmployeeId;
END

A stored procedure must contains at least three parts: stored procedure name, parameter list and its body.

The CREATE PROCEDURE is similar to CREATE TABLE or INDEX statement. It is actually an SQL statement. The CREATE PROCEDURE will force the database server add the stored procedure to the database catalog. The name of stored procedure is followed after the CREATE PROCEDURE statement, in this case it is Delete_Employee. It would be the best that the name is meaningful and follows by the naming convention of the database server specification, for example each stored procedure should begin with "sp". For most Relation database product, the name of stored procedure must be unique.

The second part is parameter list, in this case the list contains only one parameter @EmployeeId (the employee identity). Microsoft SQL Server required prefix @ for every parameters and variables of stored procedure. Followed each parameter is its type, in this case, its type is integer.

The stored procedure body starts with keywords BEGIN and ends with keyword END. In this example the body is very simple. It deletes employee by employee identity.

When all syntax statements inside body are correct, the database server will store the stored procedure in its catalog for reusing later by another stored procedure or programs.

Calling a stored procedure

We can call a stored procedure from the console window, from another stored procedure or from a program which can access database server. The syntax of calling a stored procedure is simple as follows:

EXEC spName(parameter_value_list)

The EXEC statement is used to invoke a stored procedure, after that the stored procedure name is followed in our procedure example to delete an employees with identity is 8 we can call its by following statement:

EXEC Delete_Employee(8)

If a stored procedure has more than one parameters, the values of them can be passed to it and separated by a comma.

As you see writing and calling a stored procedure is very simple and easy. In the following tutorial we will show you the feature and syntax of a stored procedure along with statement which can be used inside the body so you can empower its power. Continue reading on parameter list in stored procedure