Open In App

Stored procedures in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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. 

Syntax:

Creating a Procedure 

CREATE PROCEDURE procedure_name
(parameter1 data_type, parameter2 data_type, …)
AS
BEGIN
   — SQL statements to be executed
END

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.

Example:

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.

Query:

-- Create a new database named "SampleDB"
CREATE DATABASE SampleDB;

-- Switch to the new database
USE SampleDB;

-- Create a new table named "Customers"
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    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)
AS
BEGIN
    SELECT CustomerName, ContactName
    FROM Customers
    WHERE Country = @Country;
END;

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

Output:

CustomerName Contact Name
Naveen Tulasi

Last Updated : 20 May, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads