6.2 Input and Output Parameters

 

6.2 Input and Output Parameters

Stored procedures in SQL Server allow you to create reusable sets of one or more T-SQL statements. Input and output parameters enhance the flexibility and usability of stored procedures.

6.2.1 Creating a Stored Procedure with Input Parameters

Let's start by creating a simple stored procedure with input parameters.

-- Creating a stored procedure with input parameters
CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

6.2.2 Executing the Stored Procedure with Input Parameters

Now, let's execute the stored procedure with a specific DepartmentID value.

-- Executing the stored procedure with input parameters
EXEC GetEmployeeByDepartment @DepartmentID = 1;

EmployeeID	FirstName	LastName
101	John	Doe
102	Jane	Smith
...	...	...

In this example, the stored procedure GetEmployeeByDepartment takes an @DepartmentID parameter and returns the employees belonging to that department.

6.2.3 Creating a Stored Procedure with Output Parameters

Now, let's create a stored procedure with an output parameter.

-- Creating a stored procedure with output parameters
CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

6.2.4 Executing the Stored Procedure with Output Parameters

Executing the stored procedure with an output parameter.

-- Executing the stored procedure with output parameters
DECLARE @Count INT;
EXEC GetEmployeeCountByDepartment @DepartmentID = 1, @EmployeeCount = @Count OUTPUT;
-- Output the result
SELECT 'Employee Count' AS [Description], @Count AS [Count];

Description	Count
Employee Count	5

In this example, the stored procedure GetEmployeeCountByDepartment takes an @DepartmentID parameter and calculates the employee count for that department, storing the result in the @EmployeeCount output parameter.

This tutorial demonstrates the basics of using input and output parameters in SQL Server stored procedures. You can customize and expand on these concepts to suit your specific requirements.