Overview of Stored Procedures
- A stored procedure is a collection of one or more pre-compiled SQL statements or SQL code that you write in order to reuse the code.
- It is kept as an object on the database server. Each procedure has a name, a list of parameters, and T-SQL statements.
- Triggers, other procedures, and other applications such as Java,.Net, Python, and others can be used to invoke procedures.
- It supports nearly all relational database systems (SQL Server, Oracle, MYSQL, and so on).
- When a stored procedure is called for the first time, SQL Server creates an execution plan and stores it in cache memory.
How does the Stored procedure function?
The stored process has two types of parameters, which are as follows.
- Input parameters are values passed by the user to stored procedures.
- Output parameters are used to return the value of stored procedures.
It receives input parameters and returns numerous values to the calling process or batch in the form of output parameters.
How does the Stored procedure function?
The stored process has two types of parameters, which are as follows.
- Input parameters are values passed by the user to stored procedures.
- Output parameters are used to return the value of stored procedures.
It receives input parameters and returns numerous values to the calling process or batch in the form of output parameters.
The Benefits of Preserved Procedures
- The stored procedure decreases the amount of network communication between the application and the database server.
- Reusability decreases code inconsistency and prevents unnecessary code writes.
- Security- stored procedures are always secure since they give limited access to individuals on a need-to-know basis and control which processes and actions we can execute.
- SQL injection attacks are avoided.
Advantages and disadvantages of stored procedures
- Debugging is a little challenging.
- During testing, any data mistakes in handling errors are not generated until runtime.
Stored Procedure Types
- Stored Procedures Defined by the User
- System Stored Procedures (SSPs)
Let's make an Employee table and look at some instances of how to write user-defined stored procedures:
CREATE TABLE Employee (
EMPID INT PRIMARY KEY,
EMPName VARCHAR (50),
EMPAddress VARCHAR (Max),
EMPAge INT,
EMPSalary INT
)
--Inserting multiple records in one table.
INSERT INTO Employee (EMPID, EMPName, EMPAddress, EMPAge, EMPSalary)
VALUES (101, 'Peter', 'London', 28, 65000),
(102, 'Scott', 'Manchester', 32, 55000),
(103, 'Laura', 'Leeds', 42, 95000),
(104, 'Remy', 'Liverpool', 30, 35000)
Now check the table by running a select query.
SELECT * FROM Employee;
Now let’s create a Simple Stored Procedure.
Example 1. (Simple Stored Procedure for Select operation without any parameter).
CREATE OR ALTER PROC usp_EmployeeRecords
AS
BEGIN
SELECT * FROM Employee
END
Stored procedure usp_EmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure.
EXECUTE usp_EmployeeRecords;
After executing the procedure, we can see all the employee records are there in the results.
Example 2. (Stored Procedure with input parameter to insert record).
Now let’s create a stored procedure that accepts input parameters insert new records into the employee table, and shows all the records of the employee table.
CREATE OR ALTER PROC usp_InsertEmployeeRecords
@EMPID INT,
@EMPName VARCHAR (50),
@EMPAddress VARCHAR (Max),
@EMPAge INT,
@EMPSalary INT
AS
BEGIN
INSERT INTO Employee VALUES (@EMPID, @EMPName, @EMPAddress, @EMPAge, @EMPSalary)
SELECT * FROM Employee --Checking if all the records inserted in Employee table or not.
END
As you can see in the above screenshot, the stored procedure usp_InsertEmployeeRecords is created. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_InsertEmployeeRecords 105, 'Maria', 'Leicester', 38, 70000
After executing the procedure, we can see a new record inserted in the employee table.
Example 3. (Stored Procedure with input parameter to update record).
Now let’s create a stored procedure that updates the records in the table.
CREATE OR ALTER PROC usp_UpdateEmployeeRecords
@EMPID INT,
@EMPAddress VARCHAR (Max),
@EMPSalary INT
AS
BEGIN
UPDATE Employee
SET EMPAddress = @EMPAddress, EMPSalary = @EMPSalary
WHERE EMPID = @EMPID
SELECT * FROM Employee WHERE EMPID = @EMPID --Giving where condition along with select clause to retrieve only updated records
END
As you can see in the above screen shot, the Stored procedure usp_UpdateEmployeeRecords was created. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_UpdateEmployeeRecords 105, 'Leicester', 75000
After executing the procedure, we can see the record is updated for EMPID “105” in the employee table.
Example 4. (Stored Procedure with input parameter to delete a record).
Now let’s create a stored procedure that deletes records from the table by passing only one parameter.
CREATE OR ALTER PROC usp_DeleteEmployeeRecords
@EMPID INT
AS
BEGIN
DELETE FROM Employee WHERE EMPID = @EMPID
SELECT * FROM Employee
END
Stored procedure usp_DeleteEmployeeRecords was created, as you can see in the above screenshot. Now let’s execute the same procedure by passing parameter values.
EXECUTE usp_DeleteEmployeeRecords 105 -- we pass the EMPID here to delete particular record
Execute deleted records
After executing the procedure, we can see the record is deleted for EMPID “105” in the employee table.
Example 5. (Stored Procedure with input and output parameters).
In programming, we create a function to return the value the same way we create a stored procedure to return the value. Now let’s create a Stored procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.
CREATE OR ALTER PROC usp_SearchEmployeeSalary
@EMPName VARCHAR (50),
@EMPSalary INT OUTPUT
AS
BEGIN
SELECT @EMPSalary = EMPSalary FROM Employee WHERE EMPName = @EMPName
END
Stored procedure usp_SearchEmployeeSalary was created, as you can see in the above screenshot. Now let’s execute the same procedure that returns the salary of the employee whose EMPName will be passed to that stored proc.
DECLARE @Result INT
EXECUTE usp_SearchEmployeeSalary 'Laura', @Result OUTPUT
SELECT @Result AS EmployeeSalary
After executing the procedure, it retrieves the employee's salary whose EMPName passed to the procedure.
Example 6. (Stored Procedure with input and output parameters).
Let’s create a procedure to count how many employees are in the table whose salary is greater than or equal to 50,000.
CREATE OR ALTER PROC usp_TotalEmployeeCount
@TotalCount INT OUTPUT
AS
BEGIN
SELECT @TotalCount = count(EMPID) FROM Employee WHERE EMPSalary >= 50000
END
After executing the procedure, it retrieves the employee's salary whose EMPName passed to the procedure.
Example 6. (Stored Procedure with input and output parameters).
Let’s create a procedure to count how many employees are in the table whose salary is greater than or equal to 50,000.
CREATE OR ALTER PROC usp_TotalEmployeeCount
@TotalCount INT OUTPUT
AS
BEGIN
SELECT @TotalCount = count(EMPID) FROM Employee WHERE EMPSalary >= 50000
END
Stored procedure usp_TotalEmployeeCount was created, as you can see in the above screenshot. Now let’s execute the same procedure that counts how many employees are there in the table whose salary is greater than or equal to 50000.
DECLARE @Result INT
EXECUTE usp_TotalEmployeeCount @Result OUTPUT
Select @Result AS TotalEmpCount
After executing the procedure, it gives the count of how many employees are there in the table whose salary is greater than or equal to 50,000.
In this article, we have learned about the stored procedure, its advantages, disadvantages, types, etc. We have also gone through the examples of how to create stored procedures with and without parameters. Hope you liked it. Please share your feedback and suggestions in the comments section.
HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
