Lesson 5	Creating a Simple Stored Procedure
Objective	Create a user-defined stored procedure.

Create a User-Defined Stored Procedure

In this lesson, you will create a simple user-defined stored procedure and see why procedures are useful: they package a repeatable operation under a single name so it can be executed consistently by applications, scripts, or DBAs.

The example below creates a procedure named dbo.usp_DeleteEmployee. It accepts an employee ID as an input parameter and deletes the matching row from the Employees table.

Note: in a real production system, deletes are often implemented as a soft delete (marking a row inactive) rather than a physical delete. This lesson uses a physical delete because it clearly demonstrates how parameters drive the procedure logic.

Example: Create the Procedure


CREATE OR ALTER PROCEDURE dbo.usp_DeleteEmployee
@EmployeeID int
AS
BEGIN
SET NOCOUNT ON;

DELETE FROM dbo.Employees
WHERE EmployeeID = @EmployeeID;
END;

CREATE OR ALTER PROCEDURE in SQL Server

Walkthrough of the Example

dbo.usp_DeleteEmployee is the procedure name. The dbo schema is explicit, and the usp_ prefix is a common convention for user-defined procedures.
@EmployeeID is an input parameter. The caller supplies the employee ID to target the correct row.
int is the parameter datatype. Every parameter must declare a datatype that matches the data you expect to receive.
The value passed into @EmployeeID is referenced inside the SQL statement, so the procedure can act on the specific employee requested by the caller.
The WHERE clause limits the DELETE operation. Without a WHERE clause, a DELETE would remove all rows in the table.

When you run the CREATE OR ALTER PROCEDURE statement, SQL Server compiles and stores the procedure definition in the database. This does not execute the procedure body. The delete occurs only when you later call the procedure with EXEC.

Executing the Procedure

To execute the procedure and delete a specific employee, call it like this:

EXEC dbo.usp_DeleteEmployee @EmployeeID = 42;

Extending the Procedure Safely

A common enhancement is to validate that the employee exists before attempting the delete and to return a clear error when the ID is invalid. In modern SQL Server code, prefer THROW over RAISERROR for new development.

CREATE OR ALTER PROCEDURE dbo.usp_DeleteEmployee
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        THROW 50001, 'Employee ID does not exist.', 1;
    END;

    DELETE FROM dbo.Employees
    WHERE EmployeeID = @EmployeeID;
END;

Creating Stored Procedures - Exercise

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