Using Output Parameters in a Stored Procedure

Execute a Stored Procedure with Output Parameters

Write a SQL query to create and execute a stored procedure that uses output parameters.

Solution:

-- Create a stored procedure with an output parameter.
CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentID INT, -- Input parameter for the department ID.
    @EmployeeCount INT OUTPUT -- Output parameter for the count.
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END;

-- Execute the stored procedure.
DECLARE @Count INT;
EXEC GetEmployeeCountByDepartment @DepartmentID = 1, @EmployeeCount = @Count OUTPUT;
PRINT 'Employee Count: ' + CAST(@Count AS VARCHAR);

Explanation:

1. Purpose of the Query :

1. The goal is to create a stored procedure that calculates the number of employees in a department and returns the result via an output parameter.

2. Key Components :

1. OUTPUT Parameter : Allows the procedure to return a value to the caller.
2. EXEC : Executes the stored procedure and retrieves the output.

3. Why use Output Parameters? :

1. Output parameters provide a way to return values from stored procedures without using result sets.

4. Real-World Application :

1. For example, in HR systems, you might use this procedure to determine the size of each department.

Additional Notes:

• Output parameters are useful for returning single values or status codes.
• Combine this exercise with error handling to demonstrate robust stored procedure design.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a stored procedure that uses output parameters to return both the count and the average salary of employees in a department.
• Write a SQL query to create a stored procedure that takes an employee ID as input and returns their full name and department name using output parameters.
• Write a SQL query to create a stored procedure that calculates the total number of orders placed by a customer and returns it via an output parameter.
• Write a SQL query to create a stored procedure that checks if a given email exists in the database and returns a boolean result using an output parameter.

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

Previous SQL Exercise: Create a Table-Valued Function
Next SQL Exercise: Create a Stored Procedure with Error Handling.