|Lesson 5||Creating stored procedures, Part 2|
|Objective||Creating a user-defined stored procedure. |
Creating a User-defined Stored 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.
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
WHERE EmployeeID = @EmployeeID
RAISERROR ('Employee ID does not exist', 16, 1)
Creating Stored Procedures - Exercise