SQL Stored Procedures

A stored procedure in SQL Server is a set of SQL statements that can be saved and reused. It is similar to a function in other programming languages. Stored procedures can accept input parameters, return output parameters, and provide a way to encapsulate logic, ensuring code reusability, security, and easier maintenance.

Creating a Simple Stored Procedure

Here is a basic example of creating and using a stored procedure in SQL Server:

Example 1: Simple Stored Procedure without Parameters

1. Creating the Stored Procedure

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees
END

2. Executing the Stored Procedure

EXEC GetEmployees

Output:

Stored Procedure with Input Parameters

1. Creating the Stored Procedure with Parameters

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID
END

2. Executing the Stored Procedure with Parameters

EXEC GetEmployeeByID @EmployeeID = 1

Output:

Stored Procedure with Output Parameters

1. Creating the Stored Procedure with Output Parameters

CREATE PROCEDURE GetEmployeeName
    @EmployeeID INT,
    @FirstName NVARCHAR(50) OUTPUT,
    @LastName NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @FirstName = FirstName, @LastName = LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

2. Executing the Stored Procedure and Retrieving Output Parameters

DECLARE @FirstName NVARCHAR(50)
DECLARE @LastName NVARCHAR(50)

EXEC GetEmployeeName @EmployeeID = 1, @FirstName = @FirstName OUTPUT, @LastName = @LastName OUTPUT

SELECT @FirstName AS FirstName, @LastName AS LastName

Output:

Stored Procedure with Input and Output Parameters

1. Creating the Stored Procedure

CREATE PROCEDURE CalculateBonus
    @EmployeeID INT,
    @BonusAmount DECIMAL(10, 2) OUTPUT
AS
BEGIN
    DECLARE @Salary DECIMAL(10, 2)

    SELECT @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID

    SET @BonusAmount = @Salary * 0.10
END

2. Executing the Stored Procedure and Retrieving Output Parameters

DECLARE @BonusAmount DECIMAL(10, 2)

EXEC CalculateBonus @EmployeeID = 1, @BonusAmount = @BonusAmount OUTPUT

SELECT @BonusAmount AS BonusAmount

Output:

Benefits of Using Stored Procedures

1. Performance: Stored procedures are cached by SQL Server, reducing the need for repeated parsing and optimizing.
2. Security: Access can be restricted to the stored procedures, hiding the underlying tables and views.
3. Maintainability: Logic can be encapsulated in stored procedures, making it easier to manage and update.
4. Reusability: Common operations can be encapsulated into stored procedures, promoting code reuse.

These examples and explanations provide a basic understanding of how to create and use stored procedures in SQL Server. You can create more complex stored procedures by including error handling, transactions, and more complex business logic.