How do you create a stored procedure that accepts parameters and returns a result set?
QuinnLw
Posted by QuinnLw
Last Updated: June 20, 2024
Stored Procedure
Creating a stored procedure that accepts parameters and returns a result set depends on the database management system (DBMS) you're using. Below, I'll provide an example using Transact-SQL (T-SQL) for Microsoft SQL Server, as well as an example for MySQL, which are two commonly used DBMSs.
Microsoft SQL Server Example
Here's how you can create a stored procedure in SQL Server that takes parameters and returns a result set: 
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT
AS
BEGIN
    SET NOCOUNT ON;  -- Prevents extra result sets from interfering with SELECT statements

    SELECT EmployeeId, FirstName, LastName, DepartmentId
    FROM Employees
    WHERE DepartmentId = @DepartmentId;
END
Explanation:
- CREATE PROCEDURE GetEmployeesByDepartment: This line defines a new stored procedure named GetEmployeesByDepartment. - @DepartmentId INT: This is an input parameter for the stored procedure of the type integer. - BEGIN ... END: This defines the block of code that will execute when the procedure is called. - The SELECT statement returns the result set based on the provided department ID.
To Execute the Stored Procedure:
You can execute the procedure like this: 
EXEC GetEmployeesByDepartment @DepartmentId = 1;
MySQL Example
Here's how to create a stored procedure in MySQL that accepts parameters and returns a result set: 
DELIMITER $$

CREATE PROCEDURE GetEmployeesByDepartment(IN DepartmentId INT)
BEGIN
    SELECT EmployeeId, FirstName, LastName, DepartmentId
    FROM Employees
    WHERE DepartmentId = DepartmentId;
END $$

DELIMITER ;
Explanation:
- DELIMITER $$: This changes the statement delimiter from the default ; to $$ so that MySQL can understand the end of the procedure definition. - CREATE PROCEDURE GetEmployeesByDepartment(IN DepartmentId INT): This defines a stored procedure that takes one input parameter. - BEGIN ... END: This block contains the SQL statements to execute. - The SELECT statement retrieves employees based on the department ID.
To Execute the Stored Procedure:
You can execute it like this: 
CALL GetEmployeesByDepartment(1);
Summary
- Define the procedure using CREATE PROCEDURE. - Specify input (and/or output) parameters as needed. - Use a SELECT statement to build your result set. - Call the procedure using EXEC or CALL, depending on the DBMS. Always make sure to consult the documentation of your particular DBMS for additional options and best practices!
Default Image