Stored Procedures in SQL Server

Stored Procedures in SQL Server

Introduction:
Stored Procedures are precompiled and stored SQL code blocks that can be executed in the SQL Server database. They offer several advantages such as improved performance, code reusability, and better security. In this documentation, we'll explore the concept of Stored Procedures, their benefits, and provide examples to demonstrate their usage.

Table of Contents:

1. What are Stored Procedures?
   1.1. Definition
   1.2. Advantages of Stored Procedures

2. Creating a Stored Procedure
   2.1. Syntax
   2.2. Parameters
   2.3. Example

3. Executing a Stored Procedure
   3.1. Executing without Parameters
   3.2. Executing with Parameters
   3.3. Example

4. Modifying a Stored Procedure
   4.1. Altering a Stored Procedure
   4.2. Dropping a Stored Procedure
   4.3. Example

---

1. What are Stored Procedures?

1.1. Definition:
Stored Procedures are a set of precompiled SQL statements that are stored in the database. They are typically used to encapsulate complex queries or business logic, providing a single entry point to execute these operations.

1.2. Advantages of Stored Procedures:

• Improved Performance: Stored Procedures are precompiled and stored in the database, which reduces parsing and optimization overhead, resulting in faster execution times.
• Code Reusability: Stored Procedures can be called from multiple applications or scripts, promoting code reusability and maintainability.
• Enhanced Security: By using Stored Procedures, you can grant execution permissions on the procedure while keeping underlying tables or views secured from direct access.
• Simplified Maintenance: Since the logic is stored centrally, any modifications or updates can be done in one place, simplifying maintenance tasks.
• Transaction Management: Stored Procedures can be used to define transaction boundaries, ensuring data integrity and consistency.

---

2. Creating a Stored Procedure

2.1. Syntax:

sql
CREATE PROCEDURE procedure_name
    @param1 data_type,
    @param2 data_type = default_value, -- Optional parameter with default value
    ...
AS
BEGIN
    -- SQL statements and logic here
END

2.2. Parameters:

• procedure_name: The name of the Stored Procedure.
• @param: Input parameters that the Stored Procedure may accept. These are optional.

2.3. Example:
Let's create a simple Stored Procedure that retrieves employee details based on the department:

sql
CREATE PROCEDURE GetEmployeesByDepartment
    @departmentName NVARCHAR(50)
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE Department = @departmentName;
END

---

3. Executing a Stored Procedure

3.1. Executing without Parameters:
To execute a Stored Procedure without parameters, you simply call its name:

sql
EXEC procedure_name;

3.2. Executing with Parameters:
To execute a Stored Procedure with parameters, provide the parameter values:

sql
EXEC procedure_name @param1_value, @param2_value;

3.3. Example:
Execute the previously created Stored Procedure to get employees from the "Sales" department:

sql
EXEC GetEmployeesByDepartment 'Sales';

---

4. Modifying a Stored Procedure

4.1. Altering a Stored Procedure:
To modify a Stored Procedure, use the ALTER PROCEDURE statement:

sql
ALTER PROCEDURE procedure_name
    @param1 new_data_type
AS
BEGIN
    -- Updated SQL statements and logic here
END

4.2. Dropping a Stored Procedure:
To remove a Stored Procedure from the database, use the DROP PROCEDURE statement:

sql
DROP PROCEDURE procedure_name;

4.3. Example:
Let's modify the previous Stored Procedure to add an additional parameter:

sql
ALTER PROCEDURE GetEmployeesByDepartment
    @departmentName NVARCHAR(50),
    @status INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE Department = @departmentName AND Status = @status;
END

And if you want to remove this Stored Procedure:

sql
DROP PROCEDURE GetEmployeesByDepartment;

---

Conclusion:
Stored Procedures in SQL Server provide a powerful way to encapsulate and manage SQL code in the database. They offer performance improvements, code reusability, and enhanced security. By following the provided examples, you can create, execute, modify, and drop Stored Procedures as needed to improve your database management and application development processes.