SQL Stored Procedures Tutorial

Example Table

We will use the following table named employees for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        department VARCHAR(50),
        salary DECIMAL(10, 2)
    );

    INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
    (1, 'John', 'Doe', 'HR', 50000.00),
    (2, 'Jane', 'Smith', 'Finance', 60000.00),
    (3, 'Emily', 'Jones', 'IT', 70000.00),
    (4, 'Michael', 'Brown', 'IT', 80000.00),
    (5, 'Sarah', 'Davis', 'Finance', 55000.00);
    

Employees Table

employee_id	first_name	last_name	department	salary
1	John	Doe	HR	50000.00
2	Jane	Smith	Finance	60000.00
3	Emily	Jones	IT	70000.00
4	Michael	Brown	IT	80000.00
5	Sarah	Davis	Finance	55000.00

Using SQL Stored Procedures

A stored procedure is a prepared SQL code that you can save and reuse. In this tutorial, we will create and use stored procedures.

Example: Creating a Stored Procedure

To create a stored procedure that selects all employees from the employees table:

    CREATE PROCEDURE GetAllEmployees
    AS
    BEGIN
        SELECT * FROM employees;
    END;
    

Result:

Query	CREATE PROCEDURE GetAllEmployees AS BEGIN SELECT * FROM employees; END;
Result	Creates a stored procedure named GetAllEmployees.

Example: Executing a Stored Procedure

To execute the stored procedure GetAllEmployees:

    EXEC GetAllEmployees;
    

Result:

Query	EXEC GetAllEmployees;
Result	Returns all rows and columns from the employees table.

Example: Creating a Stored Procedure with Parameters

To create a stored procedure that selects employees by department:

    CREATE PROCEDURE GetEmployeesByDepartment
        @Department VARCHAR(50)
    AS
    BEGIN
        SELECT * FROM employees WHERE department = @Department;
    END;
    

Result:

Query	CREATE PROCEDURE GetEmployeesByDepartment @Department VARCHAR(50) AS BEGIN SELECT * FROM employees WHERE department = @Department; END;
Result	Creates a stored procedure named GetEmployeesByDepartment with a parameter for department.

Example: Executing a Stored Procedure with Parameters

To execute the stored procedure GetEmployeesByDepartment for the IT department:

    EXEC GetEmployeesByDepartment @Department = 'IT';
    

Result:

Query	EXEC GetEmployeesByDepartment @Department = 'IT';
Result	Returns all rows and columns for employees in the IT department.