SQL Stored Procedure Example | Stored Procedure In SQL
SQL stored procedure is the prepared SQL code that is used to save time and can be reused over and over again. The stored procedure code is stored in the database and helps to get the required output by passing parameters. These stored procedures are used in PL/SQL or SQL SERVER.
PL/SQL is a block-structured query language that helps developers to combine the power of SQL with procedural statements.
SQL Stored Procedure
A stored procedure is the prepared SQL code that you can save so that the code can be reused over and over again.
So if you have the SQL query that you write over and over again, save it as the stored procedure, and then call it to execute it.
You can also pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed.
The procedure can be a function or a method that can be invoked through triggers, applications in java or PHP, etc.
Create procedure <procedure_name> AS Begin <SQL STATEMENT> End Go
Let’s understand the above syntax with a proper example:
Consider table CUSTOMERS:
CREATE PROCEDURE Sample AS SELECT * FROM Testdb.Customers Go;
So, here we have created a procedure named Sample which displays the details of the customer’s table which was stored in the Testdb database.
#MODIFYING THE EXISTING PROCEDURE
We can modify the procedure using the ALTER statement.
ALTER procedure <procedure_name> AS Begin <SQL STATEMENT> End Go
ALTER PROCEDURE Sample AS SELECT * FROM Testdb.New_Customers Go;
So, here, the customers’ table data will not be displayed in New_customers; instead, data will be displayed, which was previously explicitly created in the Testdb database.
#DROPPING A PROCEDURE:
The created procedure can be dropped using the DROP keyword.
DROP PROCEDURE procedure_name;
DROP PROCEDURE sample;
So, here, the procedure named sample is dropped, which was previously created above.
Let’s look at the disadvantages of Procedures.
#Stored Procedure With One Parameter
The following SQL statement creates the stored procedure that selects Customers from a particular City from the “Customers” table:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO;
Execute the stored procedure above as follows:
EXEC SelectAllCustomers City = "Marbella";
#Stored Procedure With Multiple Parameters
Setting up multiple parameters is very easy. Just list each parameter and the datatype separated by the comma as shown below.
The following SQL statement creates a stored procedure that selects Customers from a particular City with a specific PostalCode from the “Customers” table:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO;
Execute the stored procedure above as follows.
EXEC SelectAllCustomers City = "Perth", PostalCode = "6021";
#Advantages of Stored Procedure
- They result in the improvement of the performance of the article. Whenever a procedure is being called frequently in an application in a single connection, the compiled version of that procedure is delivered.
- They reduce traffic between the database and application as the lengthy statements are already fed into the database and do not require to be sent again and again by the application.
- They are like code reusability as that of functions and methods in C/C++ and JAVA.
- Debugging of the stored procedure is not supported by MySQL.
- Stored procedures result in a lot of memory usage.
It groups one or more Transact-SQL statements into the logical unit and is stored as an object in the Database Server. When the stored procedure is called for the first time, MySQL creates the execution plan and stores it in the plan cache.
In the subsequent executions of that stored procedure, MySQL reuses a plan so that the stored procedure can execute very fast with reliable performance.
Finally, the Stored Procedure In SQL Tutorial is over.