Benefits of Stored Procedures in SQL

Benefits of Using a Stored Procedure in SQL

• Stored procedures are reusable, allowing multiple users and applications to use them by simply calling the procedure.
• Easy to modify, as changes can be made quickly using the ALTER TABLE command.
• Enhance security by restricting direct access to tables and allowing only authorized access.
• Reduce network traffic, as only the procedure name is passed instead of the entire query.
• Improve performance, as the plan for the stored procedure is created and stored in the buffer pool for quick execution.

Creating a Simple Stored Procedure in SQL

• The syntax for creating a stored procedure in SQL is: CREATE or REPLACE PROCEDURE name(parameters) AS variables; BEGIN; //statements; END;
• Parameters can be IN (receive input values), OUT (send output values), or IN OUT (receive and send values).

Creating a Stored Procedure with Parameters

• Example: CREATE PROCEDURE GetCarDesc_Para (@CID INT) AS BEGIN SET NOCOUNT ON SELECT C.CarID, C.CarName, CD.CarDescription FROM Car C INNER JOIN CarDescription CD ON C.CarID = CD.CarID WHERE C.CarID = @CID END
• Execute the procedure using EXEC GetCarDesc_Para 201;

Creating a Stored Procedure with Default Parameters

• Example: CREATE PROCEDURE GetCarDesc_DefPara (@CID INT = 301) AS BEGIN SET NOCOUNT ON SELECT C.CarID, C.CarName, CD.CarDescription FROM Car C INNER JOIN CarDescription CD ON C.CarID = CD.CarID WHERE C.CarID = @CID END
• Execute the procedure with or without passing an argument, and it will take the default value or the passed value accordingly.

Stored Procedures in SQL

• A stored procedure is a group of SQL statements stored together in a database.
• It can perform one or multiple DML operations on the database and return values based on the statements and parameters passed.
• Enables reusability by allowing multiple users to pass the same statements multiple times.