Implementing Multiple Value Returns in SQL Server User-Defined Functions

Core Implementation of Table-Valued Functions

In SQL Server, user-defined functions typically return only single scalar values, but real-world development scenarios often require returning multiple related data points. Table-valued functions provide an elegant solution to this requirement. These functions can return complete table structures containing any number of columns and rows, perfectly addressing the need for multiple value returns.

Table-valued functions are categorized into two main types: inline table-valued functions and multi-statement table-valued functions. Inline table-valued functions define the returned table structure through a single SELECT statement, offering superior performance characteristics. The basic syntax structure is as follows:

CREATE FUNCTION dbo.GetEmployeeDetails(@EmployeeID INT)
RETURNS TABLE
AS
RETURN
    SELECT 
        EmployeeName,
        Department,
        Salary,
        HireDate
    FROM Employees
    WHERE EmployeeID = @EmployeeID

When calling table-valued functions, they can be used like ordinary table queries:

SELECT * FROM dbo.GetEmployeeDetails(123)

Alternative Approach Using Stored Procedure Output Parameters

Although stored procedures are not functions, they can serve as alternatives for returning multiple values in certain scenarios. Stored procedures support output parameters that allow simultaneous return of multiple scalar values. This approach benefits from direct variable assignment for subsequent processing.

Basic implementation example of stored procedure output parameters:

CREATE PROCEDURE dbo.GetEmployeeInfo
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT,
    @Department NVARCHAR(50) OUTPUT,
    @Salary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SELECT 
        @EmployeeName = EmployeeName,
        @Department = Department,
        @Salary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID
END

Calling the stored procedure and retrieving output parameters:

DECLARE @Name NVARCHAR(100), @Dept NVARCHAR(50), @Sal DECIMAL(10,2)
EXEC dbo.GetEmployeeInfo 123, @Name OUTPUT, @Dept OUTPUT, @Sal OUTPUT
SELECT @Name AS EmployeeName, @Dept AS Department, @Sal AS Salary

Simplified Implementation with Inline Functions

Inline table-valued functions provide the most concise implementation for multiple value returns. Unlike multi-statement table-valued functions, inline functions don't contain function bodies and directly specify the returned table structure through RETURN statements. This implementation offers better query optimization performance since SQL Server can inline function calls directly into main queries.

Typical implementation pattern for inline functions:

CREATE FUNCTION dbo.FormatContactInfo(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
    SELECT 
        @FirstName + ' ' + @LastName AS FullName,
        LEN(@FirstName + @LastName) AS NameLength,
        UPPER(LEFT(@FirstName, 1)) + UPPER(LEFT(@LastName, 1)) AS Initials

This implementation is particularly suitable for scenarios requiring calculation of multiple related values based on input parameters.

Performance Comparison and Best Practices

When selecting multiple value return solutions, considerations should include performance, maintainability, and usage scenarios. Table-valued functions perform better in complex queries and join operations, while stored procedure output parameters are more appropriate when procedural logic is required.

Performance testing indicates that inline table-valued functions typically deliver optimal execution efficiency due to better optimization by the query optimizer. Multi-statement table-valued functions, while more feature-rich, may introduce additional performance overhead.

Recommended development practices include: prioritizing inline table-valued functions for simple multiple value return requirements; considering multi-statement table-valued functions when complex business logic is needed; and using stored procedure output parameter solutions only when procedural control or transaction handling is required.