Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Database Stored Procedures and SQL PSM
Stored Procedures are essential to database management systems (DBMS) because they boost security, increase performance, and encourage code reuse. A group of SQL statements are included within pre-compiled database objects known as stored procedures. They can be called by apps or other database objects and are saved in the database. In this post, we will go into the details of SQL Persistent Stored Modules (PSM), a procedural programming language extension for SQL, and examine the idea of stored procedures.
Understanding Stored Procedures
Stored Procedures are database objects that have already been pre-compiled and include a collection of SQL statements. They can be called by apps or other database objects and are saved in the database. Let's look at a straightforward SQL Server stored procedure example −
CREATE PROCEDURE GetEmployeeById
@EmployeeId INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeId = @EmployeeId
END
Input Table -Employees
+------------+--------------+------------+ | EmployeeId | EmployeeName | Department | +------------+--------------+------------+ | 1 | John Doe | HR | | 2 | Jane Smith | IT | | 3 | Mike Johnson | Sales | | 4 | Sarah Adams | Marketing | | 5 | Robert Brown | Finance | | 6 | Lisa Davis | HR | | 7 | David Wilson | IT | | 8 | Emily Lee | Sales | | 9 | Michael Chen | Marketing | | 10 | Olivia Clark | Finance | +------------+--------------+------------+
The "GetEmployeeById" stored procedure, which accepts the input parameter @Employee Id, is defined in the code above. Based on the provided Employee Id, the process extracts the employee information from the "Employees" table.
The following code may be used to run the stored procedure −
EXEC GetEmployeeById @EmployeeId = 1
Output Table
+------------+--------------+------------+ | EmployeeId | EmployeeName | Department | +------------+--------------+------------+ | 1 | John Doe | HR | +------------+--------------+------------+
With Employee Id equal to 1, this will launch the stored procedure and obtain the employee's information.
Benefits of utilizing stored procedures
Better Performance − saved Procedures are pre-compiled and saved in the database in compiled form, which lowers the overhead associated with parsing and compilation. Compared to SQL statements that are produced dynamically, this leads to speedier execution.
Code Reusability − Modular programming and code reuse are encouraged by stored procedures. They may be called from many programs or other stored procedures, which lessens duplication and encourages consistency.
Security − By enabling access control at the procedure level, stored procedures give an extra degree of security. Applications may be allowed to run particular operations while having direct access to tables restricted.
Data Integrity − Data integrity can be more successfully ensured by enclosing intricate data modification algorithms into Stored Procedures. Since the logic is contained within the database, this provides trustworthy and consistent outcomes.
Adding Procedural Capabilities to SQL (SQL PSM)
In addition to the SQL language for procedural programming is called SQL Persistent Stored Modules (PSM). It enables advanced data processing and manipulation by letting developers construct functions and procedures inside the database. Let's examine some of the salient characteristics of SQL PSM using real-world examples.
Procedural Constructs −
Procedure-Specific Markup Language (PSM) provides procedural components including conditional statements (IF, CASE), loops (WHILE, FOR), and exception handling (TRY-CATCH). Take a look at the sample below to see how conditional statements are used in PSM −
Example
CREATE PROCEDURE GetEmployeeSalaryRange
@MinSalary DECIMAL(10,2),
@MaxSalary DECIMAL(10,2)
AS
BEGIN
IF @MinSalary <= @MaxSalary
BEGIN
SELECT * FROM Employees WHERE Salary BETWEEN @MinSalary AND @MaxSalary
END
ELSE
BEGIN
RAISERROR('Invalid salary range.', 16, 1)
END
END
Input Table -Employees
| EmployeeId | EmployeeName | Salary | |------------|--------------|-----------| | 1 | John Doe | 50000.00 | | 2 | Jane Smith | 65000.00 | | 3 | Mike Johnson | 75000.00 | | 4 | Lisa Davis | 45000.00 | | 5 | Mark Wilson | 80000.00 | | 6 | Sarah Brown | 55000.00 | | 7 | Alex Lee | 60000.00 | | 8 | Emily Clark | 70000.00 | | 9 | David Jones | 40000.00 | | 10 | Olivia Smith | 90000.00 |
Output Table
Result Suppose the procedure GetEmployeeSalaryRange is called with the input @MinSalary = 50000.00 and @MaxSalary = 70000.00.
| EmployeeId | EmployeeName | Salary | |------------|--------------|-----------| | 1 | John Doe | 50000.00 | | 2 | Jane Smith | 65000.00 | | 3 | Mike Johnson | 75000.00 | | 6 | Sarah Brown | 55000.00 | | 7 | Alex Lee | 60000.00 | | 8 | Emily Clark | 70000.00 |
The input parameters @MinSalary and @MaxSalary are accepted by the stored procedure "GetEmployeeSalaryRange" in this code. It conditionally retrieves workers whose salaries are inside the given range using an IF statement. The RAISERROR statement generates an error if the @MinSalary is higher than the @MaxSalary.
Variable Support −
PSM permits the definition and use of variables, which can retain input/output values or be used to store intermediate outcomes. Let's take a look at an instance where a stored procedure uses variables to complete a calculation −
CREATE PROCEDURE CalculateTotalSalary
@EmployeeId INT,
@BonusPercentage DECIMAL(5,2) OUTPUT,
@TotalSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
DECLARE @BaseSalary DECIMAL(10,2)
SELECT @BaseSalary = Salary FROM Employees WHERE EmployeeId = @EmployeeId
SET @BonusPercentage = 0.1
SET @TotalSalary = @BaseSalary + (@BaseSalary * @BonusPercentage)
END
The "CalculateTotalSalary" stored procedure in this code determines an employee's total compensation by multiplying it by the bonus %. The basic pay for the employee is retrieved from the "Employees" table using the input parameter @Employee Id. The computed bonus % and total salary are stored, respectively, in the output parameters @Bonus Percentage and '@Total Salary'.
We can use the following code to run the stored procedure and get the computed values −
DECLARE @Bonus DECIMAL(5,2) DECLARE @Total DECIMAL(10,2) EXEC CalculateTotalSalary @EmployeeId = 1, @BonusPercentage = @Bonus OUTPUT, @TotalSalary = @Total OUTPUT SELECT @Bonus AS BonusPercentage, @Total AS TotalSalary
Input Table -Employees
| EmployeeId | Salary | |------------|---------| | 1 | 5000.00 | | 2 | 6000.00 | | 3 | 4500.00 | | 4 | 7000.00 | | 5 | 5500.00 | | 6 | 8000.00 | | 7 | 4000.00 | | 8 | 6500.00 | | 9 | 7500.00 | | 10 | 5200.00 |
Output Table
| EmployeeId | BonusPercentage | TotalSalary | |------------|-----------------|-------------| | 1 | 0.10 | 5500.00 | | 2 | 0.10 | 6600.00 | | 3 | 0.10 | 4950.00 | | 4 | 0.10 | 7700.00 | | 5 | 0.10 | 6050.00 | | 6 | 0.10 | 8800.00 | | 7 | 0.10 | 4400.00 | | 8 | 0.10 | 7150.00 | | 9 | 0.10 | 8250.00 | | 10 | 0.10 | 5720.00 |
Error Handling −
The TRY-CATCH construct in PSM contains reliable error-handling techniques. Let's look at an illustration of how TRY-CATCH is used in a stored procedure to handle errors −
CREATE PROCEDURE DivideNumbers
@Dividend INT,
@Divisor INT
AS
BEGIN
BEGIN TRY
SELECT @Dividend / @Divisor AS Result
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
The division of two numbers, @Dividend and @Divisor, is carried out using the stored procedure "Divide Numbers" in this code. The division is attempted in the TRY block, and if there is a problem, the CATCH block is activated, retrieving and displaying the error information using the ERROR_NUMBER() and ERROR_MESSAGE() methods.
Input Table -Employees
+----------+---------+ | Dividend | Divisor | +----------+---------+ | 10 | 2 | | 20 | 4 | | 15 | 3 | | 30 | 5 | | 12 | 4 | | 18 | 6 | | 25 | 5 | | 16 | 2 | | 35 | 7 | | 40 | 8 | +----------+---------+
The following code may be used to run the stored procedure and handle any errors −
EXEC DivideNumbers @Dividend = 10, @Divisor = 0
Output Table
+--------------+---------------------------------+ | ErrorNumber | ErrorMessage | +--------------+---------------------------------+ | 8134 | Divide by zero error encountered. | +--------------+---------------------------------+
A division by zero error will arise from this, and the CATCH block will be activated to show the error number, and message.
Function Definition − Within the database, functions may be defined using SQL Persistent Stored Modules (PSM). Reusable code blocks known as functions take input arguments, carry out certain actions and return a single value. Like any other SQL expression, they may be used in SQL queries. Here is an illustration of how to define a function in SQL PSM −
CREATE FUNCTION GetEmployeeCountByDepartment(departmentId INT)
RETURNS INT
BEGIN
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM Employees WHERE DepartmentId = departmentId
RETURN @Count
END
The "GetEmployeeCountByDepartment" function, which receives the input parameter departmentId, is defined in the code above. The function determines how many employees there are in the chosen department and returns the figure as an integer.
Input Table -Employees
+------------+--------------+--------------+ | EmployeeId | EmployeeName | DepartmentId | +------------+--------------+--------------+ | 1 | John Doe | 1 | | 2 | Jane Smith | 1 | | 3 | Mark Johnson | 2 | | 4 | Emily Brown | 3 | | 5 | Alex Wilson | 2 | | 6 | Sarah Davis | 1 | | 7 | Mike Thompson| 3 | | 8 | Emma Lee | 2 | | 9 | James Miller | 1 | | 10 | Lily Anderson| 3 | +------------+--------------+--------------+
Departments Table
+--------------+----------------+ | DepartmentId | DepartmentName | +--------------+----------------+ | 1 | Sales | | 2 | Marketing | | 3 | Finance | | 4 | HR | | 5 | IT | +--------------+----------------+
It is possible to use the following code to utilize this method in a SQL query −
SELECT DepartmentId, GetEmployeeCountByDepartment(DepartmentId) AS EmployeeCount FROM Departments
Output Table
+--------------+---------------+ | DepartmentId | EmployeeCount | +--------------+---------------+ | 1 | 4 | | 2 | 3 | | 3 | 2 | | 4 | 0 | | 5 | 0 | +--------------+---------------+
This query runs the function "Get Employee Count By Department" for each department to obtain the associated employee count after retrieving the department IDs from the "Departments" database.
Benefits of SQL PSM
When developing databases, using SQL PSM has a number of advantages.
Enhanced Functionality − By including procedural constructs and variable support, PSM expands SQL's capabilities. This eliminates the requirement for data transportation and processing outside the DBMS by allowing developers to perform complicated business logic and data transformations right within the database
Enhanced Performance − PSM reduces the overhead of transporting data between the database and external applications by immediately executing logic within the database. As a result, network latency is decreased and performance is enhanced.
Code Reusability and Maintainability − By encapsulating logic inside procedures and functions, PSM encourages code reusability. Developers may create modular code that can be used by other apps, which cuts down on duplication and enhances maintainability
Data Integrity and Security − Data consistency and integrity are guaranteed by PSM since data processing and manipulation logic is housed within the database. PSM also enables fine-grained access control, improving security by restricting direct access to tables and only exposing essential processes to applications.
Conclusion
In conclusion, SQL PSM and stored procedures are strong tools that improve the usability, reliability, security, and data integrity of database systems. Developers may increase the speed of their applications, optimize their code, and guarantee reliable and secure data operations within the database by utilizing these functionalities. Stored Procedures and SQL PSM offer a strong basis for effective and dependable database development, whether managing complicated data transformations or enforcing business rules.
502 Views
