In SQL, a parameterized procedure is a type of stored procedure that can accept input parameters. These parameters can be used to customize the behavior of the procedure and perform operations based on the input values provided.
For example, suppose we want to fetch records where the value is USA in the country column. So we'll write our SQL statement as,
SELECT *
FROM Customers
WHERE country = 'USA';And again, if we want to fetch records where the value of country is UK, we'll write our SQL statement as,
SELECT *
FROM Customers
WHERE country = 'UK';Notice that in the above two examples, everything is the same except the value to look in the country column.
So, instead of writing the same code again, we can create a stored procedure and call it with different values.
Creating a Parameterized Procedure
We create parameterized procedures using the CREATE PROCEDURE command followed by SQL commands. We specify the parameters using either the @ operator or enclosing them inside parentheses (). For example,
SQL Server
CREATE PROCEDURE ctr_customers @ctr VARCHAR(50) AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = @ctr;
PostgreSQL
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
$$;
MySQL
DELIMITER //
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
END //
DELIMITER ;
Here, ctr is the parameter which we need to pass while calling the stored procedure named ctr_customers.
Executing Parameterized Procedures
We can simply call the procedure created above whenever we want to fetch records based on values in the country column . For example,
SQL Server
-- call the stored procedure with 'USA' as parameter value
EXEC ctr_customers 'USA';
-- call the same stored procedure again with another parameter value 'UK'
EXEC ctr_customers 'UK';
PostgreSQL, MySQL
-- call the stored procedure with 'USA' as parameter value
CALL ctr_customers ('USA');
-- call the same stored procedure again with another parameter value 'UK'
CALL ctr_customers ('UK');
Multiple Parameterized Procedures
A stored procedure can also take multiple parameters. For example,
SQL Server
-- create stored procedure with cus_id and max_amount as parameters
CREATE PROCEDURE order_details @cus_id INT, @max_amount INT AS
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Customers.customer_id = @cus_id AND Orders.amount < @max_amount;
PostgreSQL
-- create stored procedure with cus_id and max_amount as parameters
CREATE PROCEDURE order_details (cus_id INT, max_amount INT)
LANGUAGE SQL
AS $$
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Customers.customer_id = cus_id AND Orders.amount < max_amount;
$$;
MySQL
-- create stored procedure with cus_id and max_amount as parameters
DELIMITER //
CREATE PROCEDURE order_details (cus_id INT, max_amount INT)
BEGIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Customers.customer_id = cus_id AND Orders.amount < max_amount;
END //
DELIMITER ;
Now, we can use the following codes to call this function,
SQL Server
EXEC order_details 4, 400;
PostgreSQL, MySQL
CALL order_details (4, 400);
Here, we've passed two numbers (4 and 400) as parameter values to the order_details procedure.
Also Read