SQL-Stored Procedure

SQL Stored Procedure

A SQL Stored Procedure is a precompiled collection of one or more SQL statements that are stored in the database. It can include queries, loops, and conditional logic to perform complex operations. Stored procedures are executed on the database server and are reusable.

In practical applications, stored procedures are employed to encapsulate business logic, elevate code reusability, and bolster security measures by regulating access to data.

Why Use Stored Procedures Instead of Queries?

1. Performance Optimization

   • Precompiled Execution - Stored procedures are precompiled and cached on the database server, making execution faster compared to ad-hoc queries that are compiled each time.
   • Reduced Network Traffic - Instead of sending multiple queries to the server, you can call a single stored procedure, reducing the amount of data sent between the application and the database.
     

2. Reusability and Modularity

   • Procedures encapsulate logic that can be reused across multiple applications or services. You define the logic once and call it whenever needed.
     

3. Security

   • Reduced SQL Injection Risk - Stored procedures use parameterized inputs, which help prevent SQL injection attacks.
   • Access Control - Permissions can be granted on stored procedures rather than the underlying tables, allowing better control over database access.
     

4. Ease of Maintenance

   • If you need to update the logic, you only need to modify the stored procedure without altering the application code.
     

5. Abstraction

   • The application does not need to know the database structure; it only calls the procedure. This helps in separating database logic from application logic.
     

6. Consistency

   • Stored procedures ensure that the same logic is applied uniformly across all uses, avoiding duplication and potential discrepancies in different parts of the application.

Example

CREATE PROCEDURE GetEmployeeDetails
    @DepartmentId INT
AS
BEGIN
    SELECT * 
    FROM Employee
    WHERE DepartmentId = @DepartmentId;
END;

You can call this procedure using:

EXEC GetEmployeeDetails @DepartmentId = 5;

What is Precompiled Execution?

Precompiled Execution refers to the process where a SQL query or procedure is parsed, compiled, and optimized by the database engine before being executed. Once compiled, the execution plan (the optimal way to retrieve or modify data) is cached in the database's memory. This means that subsequent executions of the same query or stored procedure can skip the compilation phase, leading to faster performance.

How Does Precompiled Execution Work for Stored Procedures?

1. Parsing

   • When a stored procedure is created, the database server parses the SQL code to ensure it is syntactically correct.
     

2. Compilation

   • The SQL code is converted into an execution plan, which is a series of steps that the database will follow to execute the SQL commands efficiently.
     

3. Optimization

   • The database engine analyzes various ways to execute the query (e.g., which indexes to use, how to join tables) and selects the most efficient execution plan.
     

4. Caching

   • The compiled execution plan is stored in the database's memory. When the stored procedure is called again, the cached plan is reused instead of recompiling the procedure.

Example of Precompiled Execution in Action

When executed multiple times, the database server reuses the precompiled execution plan, making it faster.

Example

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT * 
    FROM Orders 
    WHERE CustomerId = @CustomerId;
END;