Stored Procedures

SQL Stored Procedures are reusable SQL code blocks stored in the database server. They encapsulate a series of SQL statements and logic, allowing them to be executed as a single unit. Stored procedures offer several advantages, including improved performance, easier maintenance, and enhanced security. Here’s how you can create and use stored procedures with an example:

 

Creating a Stored Procedure:

Let's create a simple stored procedure that retrieves employee information based on a department name:

-- Create a stored procedure

CREATE PROCEDURE GetEmployeesByDepartment

@deptName VARCHAR(50)

AS

BEGIN

SELECT emp_id, emp_name, emp_salary FROM employees WHERE emp_dept =     @deptName;

END;

In this example:

• CREATE PROCEDURE statement defines the name (GetEmployeesByDepartment) and parameters (@deptName) of the stored procedure.
• @deptName VARCHAR(50) specifies a parameter that will be passed to the stored procedure.
• AS begins the body of the stored procedure, where SQL statements are written.
• SELECT emp_id, emp_name, emp_salary FROM employees WHERE emp_dept = @deptName; is the SQL query that retrieves employee information based on the provided department name.

 

Executing the Stored Procedure:

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

-- Execute the stored procedure

EXEC GetEmployeesByDepartment 'IT’;

This command will retrieve all employees from the 'IT' department, displaying their emp_id, emp_name, and emp_salary.

 

Modifying a Stored Procedure:

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

-- Modify the stored procedure

ALTER PROCEDURE GetEmployeesByDepartment

@deptName VARCHAR(50),

@sortBy VARCHAR(50) = 'emp_name'

AS

BEGIN

SELECT emp_id, emp_name, emp_salary

FROM employees

WHERE emp_dept = @deptName

ORDER BY

CASE WHEN @sortBy = 'emp_id' THEN emp_id

WHEN @sortBy = 'emp_salary' THEN emp_salary

ELSE emp_name

END;

END;

In this modified version, the stored procedure now accepts an additional parameter @sortBy, which determines the sorting order of the results (emp_id, emp_salary, or default emp_name).

 

Dropping a Stored Procedure:

To remove a stored procedure from the database, use the DROP PROCEDURE statement:

-- Drop the stored procedure

DROP PROCEDURE GetEmployeesByDepartment;

 

Benefits of Stored Procedures:

Performance: Stored procedures can reduce network traffic by executing multiple SQL statements in a single batch.

Security: Users can execute stored procedures without needing direct access to underlying tables, enhancing security.

Maintenance: Centralized logic in stored procedures makes it easier to update and maintain business rules.

Code Reusability: Stored procedures can be called from multiple applications or scripts, promoting code reuse.

 

Stored procedures are powerful tools in SQL that help improve database performance, maintainability, and security, especially in complex applications where data access logic needs to be encapsulated and reused efficiently.