Input and Output Parameters in SQL

In SQL, input and output parameters are used in stored procedures and functions to pass data to and from these routines. These parameters allow you to create more dynamic and reusable SQL code. Input parameters are used to pass values into a stored procedure or function, while output parameters are used to return values from them. Below, we will explore both input and output parameters with examples.

Input Parameters

An input parameter allows you to pass values into a stored procedure or function. When a procedure or function is called, the values of the input parameters are provided and used inside the body of the procedure or function.

Input parameters are defined when creating the procedure or function, and they are provided when calling the procedure or function.

The syntax for using input parameters in a stored procedure is:

        CREATE PROCEDURE procedure_name (@parameter_name data_type)
        AS
        BEGIN
            -- Use @parameter_name in the SQL code
        END;
    

Example of a stored procedure with an input parameter:

        CREATE PROCEDURE GetEmployeeDetails 
        @EmployeeID INT
        AS
        BEGIN
            SELECT Name, DepartmentID
            FROM Employees
            WHERE EmployeeID = @EmployeeID;
        END;
    

In this example, the stored procedure GetEmployeeDetails accepts an @EmployeeID as an input parameter and retrieves the employee's name and department based on the provided ID. You can execute the procedure like this:

        EXEC GetEmployeeDetails @EmployeeID = 101;
    

This will return the details for the employee with ID 101.

Output Parameters

Output parameters are used to return values from a stored procedure or function to the calling program. These parameters allow the procedure or function to provide results that can be used after the execution of the routine.

To define an output parameter, you must use the OUTPUT keyword when creating the stored procedure or function, and when calling it.

The syntax for using output parameters is:

        CREATE PROCEDURE procedure_name (@parameter_name data_type OUTPUT)
        AS
        BEGIN
            -- Set the output parameter value
            SET @parameter_name = value;
        END;
    

Example of a stored procedure with an output parameter:

        CREATE PROCEDURE GetTotalSalary 
        @DepartmentID INT, 
        @TotalSalary DECIMAL(18,2) OUTPUT
        AS
        BEGIN
            SELECT @TotalSalary = SUM(Salary)
            FROM Employees
            WHERE DepartmentID = @DepartmentID;
        END;
    

In this example, the stored procedure GetTotalSalary calculates the total salary for all employees in a specific department and returns the result via the @TotalSalary output parameter. You can call the procedure and retrieve the output value like this:

        DECLARE @Salary DECIMAL(18,2);
        EXEC GetTotalSalary @DepartmentID = 3, @TotalSalary = @Salary OUTPUT;
        PRINT @Salary;
    

This will return the total salary for all employees in department 3 and print the result.

Using Both Input and Output Parameters

In some cases, a stored procedure or function can have both input and output parameters. This allows you to pass data into the procedure or function, process the data, and return the results through output parameters.

Here is an example where both input and output parameters are used:

        CREATE PROCEDURE CalculateBonus
        @EmployeeID INT, 
        @Bonus DECIMAL(10,2) OUTPUT
        AS
        BEGIN
            DECLARE @Salary DECIMAL(18,2);
            -- Input parameter: Get the salary based on EmployeeID
            SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
            -- Output parameter: Calculate and return the bonus (10% of salary)
            SET @Bonus = @Salary * 0.10;
        END;
    

In this example, the CalculateBonus procedure accepts the employee's ID as an input parameter, retrieves their salary, calculates the bonus (10% of the salary), and returns the bonus through the @Bonus output parameter. You can call the procedure like this:

        DECLARE @BonusAmount DECIMAL(10,2);
        EXEC CalculateBonus @EmployeeID = 101, @Bonus = @BonusAmount OUTPUT;
        PRINT @BonusAmount;
    

This will calculate and print the bonus for the employee with ID 101.

Conclusion

Input and output parameters are essential tools for making stored procedures and functions more flexible and reusable. Input parameters allow you to pass data into a procedure or function, while output parameters allow you to return results. By using both types of parameters, you can create powerful SQL routines that interact dynamically with your data and applications.