Stored procedures are prepared SQL code that you save so you can reuse it over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure and call it to run it. You can also pass parameters to stored procedures so that the stored procedure can act on the passed parameter values.
Stored Procedures are created to perform one or more DML operations on Database. It is nothing but the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not return a value.
Creating a Procedure
CREATE PROCEDURE procedure_name
(parameter1 data_type, parameter2 data_type, …)
— SQL statements to be executed
To Execute the procedure
EXEC procedure_name parameter1_value, parameter2_value, ..
The most important part is the parameters. Parameters are used to pass values to the Procedure. There are different types of parameters, which are as follows:
- BEGIN: This is what directly executes or we can say that it is an executable part.
- END: Up to this, the code will get executed.
Imagine a database named “SampleDB”, a table named “Customers” with some sample data, and a stored procedure named” GetCustomersByCountry”. The stored procedure takes the parameter “Country” and returns a list of customers from the “Customers” table that matches the specified country. Finally, the stored procedure is executed with the parameter “Sri Lanka” to retrieve the list of customers from Sri Lanka.
-- Create a new database named "SampleDB"
CREATE DATABASE SampleDB;
-- Switch to the new database
-- Create a new table named "Customers"
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
-- Insert some sample data into the Customers table
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (1, 'Shubham', 'Thakur', 'India'),
(2, 'Aman ', 'Chopra', 'Australia'),
(3, 'Naveen', 'Tulasi', 'Sri lanka'),
(4, 'Aditya', 'Arpan', 'Austria'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain');
-- Create a stored procedure named "GetCustomersByCountry"
CREATE PROCEDURE GetCustomersByCountry
SELECT CustomerName, ContactName
WHERE Country = @Country;
-- Execute the stored procedure with parameter "Sri lanka"
EXEC GetCustomersByCountry @Country = 'Sri lanka';
Note: You will need to make sure that the user account has the necessary privileges to create a database. You can try logging in as a different user with administrative privileges or contact the database administrator to grant the necessary privileges to your user account. If you are using a cloud-based database service, make sure that you have correctly configured the user account and its permissions.