Learn about Stored Procedures in SQL

Introduction

A stored procedure in SQL Server is a collection of T-SQL statements that are built and saved in the database. The stored procedure takes input and output parameters, runs the SQL commands, and, if there are any results, returns a result set. When a saved procedure is run for the first time, it automatically compiles. For quicker performance, it also generates an execution plan that is utilized for consecutive runs.

Two categories of stored processes exist:

User-defined procedures: A database user can build a user-defined stored procedure in any system database, excluding the resource database, or in a user-defined database.

System procedures: SQL Server comes with system procedures that are conceptually present in the sys schema of all the databases and are physically stored in the internal, secret Resource database. The sp_ prefix is used to identify system-stored procedures.

Create Stored Procedure

To construct a stored procedure, use the CREATE statement. Select, Insert, Update, and Delete statements may all be found in a stored procedure. Here is an example of a straightforward stored procedure that uses the SELECT query to return the data from the Employee database.

CREATE PROCEDURE uspGetEmployeeList
AS
BEGIN
SELECT EmpID
,FirstName
,LastName
FROM dbo.Employee
END

The Employee table is updated using the following stored method.

CREATE PROCEDURE dbo.uspInsertEmployee
(
@FirstName nvarchar(50)
,@LastName nvarchar(50)
,@Email nvarchar(50)
,@PhoneNo nvarchar(20)
,@Salary money
)
AS
BEGIN
INSERT INTO dbo.Employee
(FirstName
,LastName
,Email
,PhoneNo
,Salary)
VALUES
(
@FirstName
,@LastName
,@Email
,@PhoneNo
,@Salary
)
END

write your code here: Coding Playground

Instead of using the Put statement, the aforementioned stored procedure may be utilized to insert entries into the Employee table. The stored process receives values as arguments. Parameter variables are prefixed with the @ sign. The EXEC keyword can be used to run the uspInsertEmployee stored procedure, as seen below.

EXEC dbo.uspInsertEmployeeDetails
@FirstName ='Swati'
,@LastName = 'Karia'
,@Email = 'swatik@test.com'
,@PhoneNo = '6657890980'
,@Salary = 300000

While running a stored procedure, specify each argument individually using commands.

View Stored Procedure

To view the text of a stored procedure that is currently in use, use sp_help or sp_helptext.

Modify Stored Procedure

To change a stored procedure, use the ALTER PROCEDURE statement.

ALTER PROCEDURE dbo.uspGetEmployees
AS
BEGIN
SELECT EmpID
,FirstName
,LastName
,Salary
FROM dbo.Employee
END

write your code here: Coding Playground

Renaming Stored Procedure

Use the system stored procedure sp_rename to rename an existing stored procedure. The following renames uspGetEmployeeList to uspGetEmployees.

sp_rename 'uspGetEmployeeList','uspGetEmployees'

Delete Stored Procedure

Use the DROP PROCEDURE statement to delete a stored procedure.

DROP PROCEDURE dbo.uspGetEmployees;

Handling Exceptions in Stored Procedures

The TRY..CATCH block is used in SQL Server to gracefully handle exceptions. A TRY block may be used to contain a collection of T-SQL queries. When an error occurs in the TRY block, control is then transferred to the CATCH block, which contains further SQL queries to deal with the problem. System methods like ERROR NUMBER(), ERROR STATE(), and ERROR SEVERITY() can be used in the CATCH block to retrieve information about errors. The uspEmpUpdate stored procedure error is handled in the example that follows.

CREATE PROCEDURE uspUpdateEmpSalary
(
@empId int
,@salary float
)
AS
BEGIN TRY
UPDATE dbo.Employee
SET Salary = @salary
WHERE EmployeeID = @empId
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

write your code here: Coding Playground

Advantages of Stored procedures

Reusable stored processes exist. The same stored procedure (SP) can be used by several users in various applications, which lowers network traffic.
Applications must call a database operation, and the database responds by communicating with the user. A stored procedure encapsulates database objects, serving as a security measure by limiting access to the database objects. decreased development costs, adaptability, and improved readability. enhances efficiency.
The database processor develops an execution plan when a stored procedure is run for the first time.
This execution plan is then utilized every time this SP is run after that.