Mastering SQL: From Fundamentals to Advanced Techniques

Learn about Stored Procedures in SQL

Board Infinity
Board Infinity
Learn about Stored Procedures in SQL

    Introduction To SQL: A Complete Guide

    SQL Commands-An Introduction

    Char and Varchar in SQL

    ORDER BY in SQL

    5 Resume Writing Myths That You Need to Stop Following Now!

    GROUP BY in SQL

    Learn How to Perform Union in SQL?

    Joins and Its Types in SQL

    Rename column name in SQL

    Learn How To Compare Two Or More Datetime Entities In SQL With Examples

    Difference Between SQL and NoSQL

    Constraints in SQL

    Learn about Aggregate Functions in SQL

    SQL Subqueries

    Nested Queries in SQL - Query In Query

    SQL WITH Clause – Tutorial and Examples

    How to Drop a Table in PostgreSQL?

    How to Perform Pivot in SQL?

    SQL Window Function

    Find Nth Highest Salary in SQL

    Working of Correlated SubQueries in SQL

    Learn about Stored Procedures in SQL

    Triggers in SQL: An Overview and Example

    PL/SQL Explained

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.