In SQL Server, stored procedures are a powerful tool for creating reusable sets of SQL commands. They offer several benefits, such as reducing network traffic, improving execution speed, and enhancing security. One important aspect of stored procedures is their ability to return values after execution.

Default Return Value

By default, when a stored procedure is executed in SQL Server, it returns an integer value. This value indicates the execution status of the stored procedure. A return value of 0 indicates successful completion, while non-zero values indicate an error.

For example, consider a stored procedure that retrieves department information:

CREATE PROCEDURE GetDepartmentInfo
    @DepartmentName VARCHAR(25)
AS
    SELECT DepartmentName, RecordDate, AverageSalary
    FROM SampleDepartments
    WHERE DepartmentName = @DepartmentName

When this stored procedure is executed, it returns two result sets. The first result set contains the department information, while the second result set displays the stored procedure return value.

Changing the Return Value

The return value of a stored procedure can be modified using the RETURN command. For example, consider a stored procedure that returns the number of records in a table:

CREATE PROCEDURE GetDepartmentRecord
AS
    DECLARE @DepartmentRow AS INT
    SELECT @DepartmentRow = COUNT(*)
    FROM SampleDepartments
    RETURN @DepartmentRow

In this case, the stored procedure return value will be the total number of rows in the SampleDepartments table.

It’s important to note that the return value data type can only be an integer and cannot be changed. Attempting to assign a different data type will result in a data type conversion error.

Using Output Parameters

Instead of using the return value, a stored procedure can also define output parameters. These parameters can have different data types and can be used to return specific values.

For example, consider a stored procedure that returns department names and the maximum record date:

CREATE PROCEDURE GetDepartmentSalaryList
    @Salary MONEY,
    @MaximumRecordDate DATETIME OUTPUT
AS
    SELECT @MaximumRecordDate = MAX(RecordDate)
    FROM SampleDepartments
    SELECT *
    FROM SampleDepartments
    WHERE AverageSalary < @Salary

In this case, the @MaximumRecordDate parameter is an output parameter that will hold the maximum record date. To call this stored procedure and retrieve the output parameter value, you need to explicitly declare a variable and assign the output parameter to it.

Handling NULL Values

In some cases, a NULL value may need to be assigned to the stored procedure return value. However, SQL Server automatically converts NULL values to 0. To handle this issue, you can use the ISNULL function before returning the value.

For example, consider a stored procedure that returns the number of departments with a salary range:

CREATE PROCEDURE GetDepartmentSalaryRange
    @Salary MONEY
AS
    DECLARE @ReturnSalary AS INT
    IF @Salary < 25000
    BEGIN
        SELECT @ReturnSalary = COUNT(*)
        FROM SampleDepartments
        WHERE AverageSalary <= @Salary
    END
    RETURN ISNULL(@ReturnSalary, 0)

In this case, if the @ReturnSalary variable is not assigned a value, the return value will be NULL. By using the ISNULL function, we can ensure that a default value of 0 is returned instead.

Conclusion

In this article, we have explored the concept of stored procedure return values in SQL Server. We have learned how to use the default return value, change the return value using the RETURN command, and use output parameters instead of the return value. Additionally, we have discussed how to handle NULL values in the return value. Stored procedure return values are a powerful feature that can enhance the functionality and flexibility of your SQL Server applications.