SQL Stored Procedures for SQL Server

Welcome to The Coding College! This tutorial explains SQL Stored Procedures, a powerful feature in SQL Server that allows you to encapsulate queries and logic into reusable, precompiled objects. Let’s dive into their structure, usage, and benefits with practical examples.

What Are SQL Stored Procedures?

A stored procedure is a precompiled collection of one or more SQL statements stored in the database. Instead of executing the same SQL repeatedly, you can call the procedure, enhancing efficiency, maintainability, and security.

Key Features of Stored Procedures

1. Reusable: Can be executed multiple times without rewriting queries.
2. Efficient: Precompiled and optimized by SQL Server.
3. Secure: Provides a layer of abstraction for sensitive operations.
4. Maintainable: Centralizes business logic for easier updates.

Syntax

Basic Stored Procedure

CREATE PROCEDURE procedure_name  
AS  
BEGIN  
    -- SQL statements  
END;  

Stored Procedure with Parameters

CREATE PROCEDURE procedure_name  
    @param1 datatype,  
    @param2 datatype = default_value  
AS  
BEGIN  
    -- SQL statements using parameters  
END;  

Executing a Stored Procedure

EXEC procedure_name;  
-- Or  
EXEC procedure_name @param1 = value1, @param2 = value2;  

Example Table: employees

Examples

1. Creating a Basic Stored Procedure

Let’s create a procedure to fetch all employees.

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

Executing the Procedure

EXEC GetAllEmployees;  

Result:

2. Stored Procedure with Input Parameters

This procedure retrieves employees from a specific department.

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

Executing the Procedure

EXEC GetEmployeesByDepartment @dept = 'IT';  

Result:

3. Stored Procedure with Output Parameters

This procedure calculates the total salary of a department.

CREATE PROCEDURE GetDepartmentSalary  
    @dept NVARCHAR(50),  
    @total_salary INT OUTPUT  
AS  
BEGIN  
    SELECT @total_salary = SUM(salary) FROM employees WHERE department = @dept;  
END;  

Executing the Procedure

DECLARE @salary INT;  
EXEC GetDepartmentSalary @dept = 'IT', @total_salary = @salary OUTPUT;  
PRINT @salary;  

Result:

150000  

4. Updating Data with a Stored Procedure

This procedure updates the salary of an employee.

CREATE PROCEDURE UpdateEmployeeSalary  
    @emp_id INT,  
    @new_salary INT  
AS  
BEGIN  
    UPDATE employees  
    SET salary = @new_salary  
    WHERE emp_id = @emp_id;  
END;  

Executing the Procedure

EXEC UpdateEmployeeSalary @emp_id = 1, @new_salary = 55000;  

Updated Table

Best Practices

1. Parameterize Procedures: Avoid hardcoding values for flexibility and security.
2. Handle Errors: Use TRY...CATCH blocks to manage runtime errors.
3. Document Your Code: Include comments to explain procedure functionality.
4. Optimize for Performance: Test and fine-tune queries for better efficiency.

Error Handling in Stored Procedures

Example: Error Handling

CREATE PROCEDURE SafeInsertEmployee  
    @name NVARCHAR(50),  
    @dept NVARCHAR(50),  
    @salary INT  
AS  
BEGIN  
    BEGIN TRY  
        INSERT INTO employees (name, department, salary)  
        VALUES (@name, @dept, @salary);  
    END TRY  
    BEGIN CATCH  
        PRINT ERROR_MESSAGE();  
    END CATCH;  
END;  

Advantages of Stored Procedures

1. Improved Performance: Precompiled queries run faster.
2. Enhanced Security: Permissions can be granted to execute procedures without exposing underlying tables.
3. Code Reusability: Write once, reuse multiple times.
4. Reduced Network Traffic: Executes on the server, minimizing data transfer.

Conclusion

SQL Stored Procedures simplify database operations by encapsulating logic into reusable units. By using input and output parameters, error handling, and efficient queries, stored procedures provide a robust way to manage complex data requirements.