Open In App
Related Articles

Stored procedures in SQL

Improve Article
Save Article
Like Article

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, ..

Parameter Explanation

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: 

  1. BEGIN: This is what directly executes or we can say that it is an executable part.
  2. 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"

-- Switch to the new database
USE SampleDB;

-- Create a new table named "Customers"
CREATE TABLE Customers (
    CustomerName VARCHAR(50),
    ContactName VARCHAR(50),
    Country VARCHAR(50)

-- 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
    @Country VARCHAR(50)
    SELECT CustomerName, ContactName
    FROM Customers
    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.


CustomerNameContact Name
Last Updated : 20 May, 2023
Like Article
Save Article
Similar Reads