Creating a Parameterized Stored Procedure in SQL

Create a Stored Procedure with Parameters

Write a SQL query to create a stored procedure that takes parameters and returns results.

Solution:

-- Create a stored procedure to retrieve employees by department.
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT -- Input parameter for the department ID.
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a reusable stored procedure that retrieves employees based on a given department ID.

2. Key Components :

1. CREATE PROCEDURE : Defines the stored procedure.
2. @DepartmentID INT : Specifies the input parameter.
3. SELECT : Retrieves data based on the input parameter.

3. Why use Stored Procedures? :

1. Stored procedures improve code reusability, security, and performance by encapsulating complex logic.

4. Real-World Application :

1. For example, in a company database, you might use this procedure to fetch employees from a specific department for reporting purposes.

Additional Notes:

• Stored procedures can accept multiple parameters and return result sets or output values.
• Use this exercise to teach the basics of parameterized queries and modular database design.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a stored procedure that accepts two parameters: start date and end date, and returns all employees hired within that period.
• Write a SQL query to create a stored procedure that calculates the total sales for a given product category.
• Write a SQL query to create a stored procedure that updates the status of an order based on the order ID.
• Write a SQL query to create a stored procedure that retrieves the top 5 highest-paid employees from a table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: SQL Stored Procedures and Functions Exercises Home
Next SQL Exercise: Create a User-Defined Function.