_edited_edited.png)
Stored Procedures are an integral part of SQL Server. They are a collection of SQL commands written and stored in the database management system that allow various users and programs to share and reuse the procedures.
When there is an SQL query that needs to be written multiple times, it can be saved as a stored procedure and then executed. This reduces the need for rewriting SQL queries. Stored procedures also accept input parameters. Based on the SQL commands in the procedure and the parameters passed, the stored procedures perform one or multiple DML operations on the database, and return values, if any.
Photo by Claudio Schwarz on Unsplash
Creating a Stored Procedure :
Stored procedures are created using the CREATE statement.
Below is an example of a simple stored procedure that uses the SELECT query to return the data from the Department table in the Adventureworks2022 database in the SQL Server Management Studio.
Once the procedure is created, you can see it in the Object Explorer, under Programmability -> Stored Procedures.
Next, to run the procedure, use the syntax: EXEC procedure_name <parameters if any>;.
Modifying a Stored Procedure
Stored Procedures are modified using the ALTER PROCEDURE statement.
Using Parameters in Stored Procedures:
Stored procedures can be made more dynamic by passing parameters. These parameters give us the flexibility to filter out the results . Here’s how you can create a stored procedure with a single parameter.
After the creation, the procedure can be executed as below which will return the records based on the filter condition passed in the input parameter.
Stored procedures can also accept multiple parameters and this allows for more complex queries. Here’s how you can create a stored procedure with multiple parameters.
The following command will execute the procedure with two input parameters and will give a greater flexibility in retrieval of records.
Deleting Stored Procedures
Whenever we need to delete stored procedures that are no longer needed, we do so by using the DROP statement.
The following command deletes a single stored procedure.
To drop multiple stored procedures using a single command, you can specify each procedure separated by a comma as shown below.
Benefits of using Stored Procedures
Client/server traffic reduction: The SQL commands in the procedure are executed as a single batch of code. This reduces the network traffic significantly as only the call to execute the procedure is sent across the network as against every time for each line of code.
Reusability: Stored procedures promote reusability as the SQL code written once can be used multiple times.
Performance Improvement: Stored procedures are precompiled, so they execute faster than individual SQL commands.
Increased Security : Stored procedures enhance security. Multiple users can be given permission to execute procedures without giving them direct access to data.
Disadvantages
Testing and Debugging: Stored procedures are difficult to test and debug compared to application testing and debugging.
Runtime validation: Errors in stored procedures can't be caught during compilation or build.
Stored Procedures are not encrypted by default so anyone who gains access to your database can see them all at once.
It is therefore important to carefully evaluate the specific requirements of a project and consider these advantages and disadvantages when deciding whether to use stored procedures or not.
Comments