Stored Procedures  «Prev  Next»

Lesson 5Creating stored procedures, Part 2
ObjectiveCreating a user-defined stored procedure.

Creating User-defined Stored Procedure

Encapsulates Procedure

In this lesson, you will practice creating a simple stored procedure. The stored procedure called usp_DeleteEmployee encapsulates the procedure of deleting an employee from the Employees table. The usp part of the stored procedure is just a prefix that I like to use indicating a user-defined stored procedure.
To code this function, we will create a stored procedure that accepts one parameter: the employee ID.
In addition, the stored procedure will be coded to remove the employee from the Employees table using the value of that parameter.
This code is demonstrated in the following SlideShow:

Executing this code will compile the stored procedure and store it on the server. Executing the code does not actually run the stored procedure, which would delete the employee from the Employees table.

Extending Stored Procedure Functionality

You can extend the functionality of the stored procedure by testing to see if the value exists in the database first.
If it does not, an error will be returned. In the next lesson, I will discuss modifying stored procedures.

CREATE PROCEDURE usp_DeleteEmployee @EmployeeID int
IF EXISTS (SELECT * FROM employees WHERE EmployeeID 
= @EmployeeID)

FROM Employees
WHERE EmployeeID = @EmployeeID
RAISERROR ('Employee ID does not exist', 16, 1)

Creating Stored Procedures - Exercise

Click the Exercise link below to practice creating stored procedures.
Creating Stored Procedures - Exercise