Creating Basic Stored Procedures

This tutorial guides you through the fundamental steps of creating and executing basic stored procedures in SQL Server. Stored procedures are precompiled SQL statements that can be executed repeatedly, offering benefits in performance, security, and code reusability.

What is a Stored Procedure?

A stored procedure is a set of one or more SQL statements that are compiled and stored on the database server. When you need to execute these statements, you simply call the procedure by its name, rather than sending the individual statements each time. This can significantly improve performance, especially for complex operations or frequently executed queries.

Benefits of Stored Procedures

Performance: Compiled and optimized on the server, leading to faster execution.
Reusability: Write code once and call it multiple times from different applications or scripts.
Security: Grant execute permissions on procedures instead of direct table access, limiting potential data breaches.
Modularity: Encapsulate business logic within the database.
Reduced Network Traffic: Only the procedure call is sent over the network, not the entire SQL script.

Creating Your First Stored Procedure

Let's create a simple stored procedure that retrieves all records from a hypothetical Employees table.

Step 1: Define the Procedure Structure

You use the CREATE PROCEDURE (or CREATE PROC) statement. If a procedure with the same name already exists, you can use OR ALTER PROCEDURE to modify it, or DROP PROCEDURE IF EXISTS before creating.

CREATE PROCEDURE usp_GetAllEmployees
        AS
        BEGIN
            -- SQL statements go here
            SELECT EmployeeID, FirstName, LastName, Department
            FROM Employees;
        END;

In this example:

CREATE PROCEDURE usp_GetAllEmployees: Declares the creation of a procedure named usp_GetAllEmployees. It's a common convention to prefix stored procedure names with usp_.
AS: Separates the procedure declaration from its body.
BEGIN...END: Encloses the statements that form the body of the procedure.
SELECT ... FROM Employees;: The actual SQL query to be executed.

Step 2: Execute the Stored Procedure

Once created, you can execute the procedure using the EXECUTE (or EXEC) statement:

EXECUTE usp_GetAllEmployees;

Or, more concisely:

EXEC usp_GetAllEmployees;

Step 3: Creating a Procedure with Parameters

Stored procedures can accept input parameters, making them more dynamic. Let's create a procedure to find employees by their last name.

CREATE PROCEDURE usp_GetEmployeeByLastName
            @LastName VARCHAR(50) -- Declare an input parameter
        AS
        BEGIN
            SELECT EmployeeID, FirstName, LastName, Department
            FROM Employees
            WHERE LastName = @LastName;
        END;

Here, @LastName is an input parameter of type VARCHAR(50). When calling the procedure, you need to provide a value for this parameter:

EXECUTE usp_GetEmployeeByLastName @LastName = 'Smith';

You can also pass parameters positionally if you know their order, but using named parameters is generally clearer and more robust:

-- Less recommended for clarity
        EXEC usp_GetEmployeeByLastName 'Jones';

Step 4: Stored Procedure with Input and Output Parameters

Procedures can also have output parameters, which allow them to return values back to the caller. Let's create a procedure that counts employees in a specific department and returns the count.

CREATE PROCEDURE usp_CountEmployeesByDepartment
            @DepartmentName VARCHAR(50),       -- Input parameter
            @EmployeeCount INT OUTPUT       -- Output parameter
        AS
        BEGIN
            SELECT @EmployeeCount = COUNT(*)
            FROM Employees
            WHERE Department = @DepartmentName;
        END;

To execute this and retrieve the output parameter, you need to declare a variable to hold the output:

DECLARE @Count INT; -- Declare a variable to store the output

        EXEC usp_CountEmployeesByDepartment
            @DepartmentName = 'Sales',
            @EmployeeCount = @Count OUTPUT; -- Pass the variable as an output parameter

        SELECT @Count AS NumberOfSalesEmployees; -- Display the result

Tip: Always define appropriate data types for your parameters and consider their length and nullability.

Modifying Stored Procedures

If you need to change an existing stored procedure, you can use ALTER PROCEDURE. This is safer than dropping and recreating, as it preserves permissions and dependencies.

ALTER PROCEDURE usp_GetAllEmployees
        AS
        BEGIN
            SELECT EmployeeID, FirstName, LastName, Department, HireDate
            FROM Employees;
        END;

Important: Be cautious when altering procedures, especially in production environments. Test your changes thoroughly.

Dropping Stored Procedures

To remove a stored procedure that is no longer needed, use the DROP PROCEDURE statement:

DROP PROCEDURE usp_GetEmployeeByLastName;

Conclusion

You've learned the basics of creating, executing, and modifying stored procedures, including how to use input and output parameters. Stored procedures are a powerful tool for building efficient, secure, and maintainable database applications.