Stored Procedures in SQL

Stored Procedures

Stored procedures in SQL are precompiled collections of SQL statements that can be executed as a single unit. They are used to encapsulate repetitive tasks, improve performance, and enhance security within a database system. By grouping multiple SQL statements into a stored procedure, you can streamline complex operations, reduce network traffic, and promote code reuse.

Creating a Basic Stored Procedure:

A basic stored procedure is created using the CREATE PROCEDURE statement. This procedure can include any valid SQL statements and is defined with a unique name.

Example:

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

This stored procedure, named GetEmployeeDetails, retrieves all employee details from the Employees table. To execute this procedure, you would use the EXEC command:

EXEC GetEmployeeDetails;

Parameterized Stored Procedures:

Parameterized stored procedures allow for more flexibility by accepting input parameters. These parameters enable the procedure to perform operations based on the input values, making it adaptable to various scenarios.

Example:

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

In this example, @EmployeeID is an input parameter that allows you to retrieve details for a specific employee by their ID. To execute this procedure with a given employee ID, you would do:

EXEC GetEmployeeByID @EmployeeID = 1;

Output Parameters in Stored Procedures:

Stored procedures can also return values through output parameters, which can be useful for retrieving specific results without using a SELECT statement.

Example:

CREATE PROCEDURE GetEmployeeDepartment
    @EmployeeID INT,
    @Department VARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @Department = Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

This procedure accepts an employee ID as an input parameter and returns the department name as an output parameter. To execute this procedure and retrieve the department name, you would declare a variable to hold the output value:

DECLARE @Dept VARCHAR(50);

EXEC GetEmployeeDepartment @EmployeeID = 1, @Department = @Dept OUTPUT;

SELECT @Dept AS Department;

In this case, the @Dept variable will contain the department name of the employee with the specified ID after the procedure is executed.