SQL Stored Procedures for SQL Server

 Stored procedures in SQL Server are precompiled SQL statements that can be executed on demand. They allow you to encapsulate logic and operations, making them reusable and efficient. Below are some key concepts and examples of how to create and use stored procedures in SQL Server.

1. Creating a Basic Stored Procedure

A basic stored procedure in SQL Server can be created using the CREATE PROCEDURE statement:

CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, JobTitle
    FROM Employees;
END;

• This procedure, named GetEmployeeDetails, retrieves basic employee information from the Employees table.

2. Creating a Stored Procedure with Parameters

Stored procedures can also accept parameters to make them dynamic. For example:

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, JobTitle
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

• The @EmployeeID parameter is passed into the procedure, and it is used in the WHERE clause to filter results.

3. Executing a Stored Procedure

Once a stored procedure is created, you can execute it using the EXEC or EXECUTE keyword:

-- Without parameters
EXEC GetEmployeeDetails;

-- With parameters
EXEC GetEmployeeByID @EmployeeID = 1;

4. Stored Procedure with Input and Output Parameters

Stored procedures can return output values via output parameters:

CREATE PROCEDURE GetEmployeeSalary
    @EmployeeID INT,
    @Salary DECIMAL(10, 2) OUTPUT
AS
BEGIN
    SELECT @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

To call this procedure and retrieve the output:

DECLARE @Salary DECIMAL(10, 2);
EXEC GetEmployeeSalary @EmployeeID = 1, @Salary = @Salary OUTPUT;
PRINT @Salary;

5. Using Control Flow in Stored Procedures

Stored procedures can include control flow logic like IF, ELSE, WHILE, etc.

CREATE PROCEDURE GetEmployeeStatus
    @EmployeeID INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        PRINT 'Employee exists.';
    END
    ELSE
    BEGIN
        PRINT 'Employee not found.';
    END
END;

6. Handling Errors in Stored Procedures

SQL Server provides TRY...CATCH blocks to handle errors:

CREATE PROCEDURE InsertEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @JobTitle NVARCHAR(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Employees (FirstName, LastName, JobTitle)
        VALUES (@FirstName, @LastName, @JobTitle);
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
END;

7. Dropping a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE statement:

DROP PROCEDURE GetEmployeeByID;

8. Alter an Existing Stored Procedure

To modify an existing stored procedure, use the ALTER PROCEDURE statement:

ALTER PROCEDURE GetEmployeeByID
    @EmployeeID INT,
    @EmployeeStatus NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @EmployeeStatus = JobTitle
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

Conclusion

Stored procedures in SQL Server are powerful tools for improving code reusability, security, and performance. They support a wide range of operations, including complex logic, error handling, and parameterization, making them ideal for encapsulating business logic.