Optional Parameters in Table Valued Function

Hi Aradhana Tripathi,

In SQL Server, table-valued functions can have optional parameters by assigning default values to those parameters. This allows flexibility in function calls where certain parameters can be omitted. Here's an example demonstrating the use of optional parameters in a table-valued function:

CREATE FUNCTION dbo.GetEmployees
(
    @DepartmentID INT,
    @IsManager BIT = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, EmployeeName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
    AND (@IsManager IS NULL OR IsManager = @IsManager)
)

Above example, the @IsManager parameter is optional with a default value of NULL. When calling the function, you can choose to provide or omit this parameter. If omitted, the function will return all employees in the specified department regardless of their managerial status.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jayraj Chhaya2y ago
Hi Aradhana Tripathi,

In SQL Server, table-valued functions can have optional parameters by assigning default values to those parameters. This allows flexibility in function calls where certain parameters can be omitted. Here's an example demonstrating the use of optional parameters in a table-valued function:
```
CREATE FUNCTION dbo.GetEmployees
(
    @DepartmentID INT,
    @IsManager BIT = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, EmployeeName
    FROM Employees
    WHERE DepartmentID = @DepartmentID
    AND (@IsManager IS NULL OR IsManager = @IsManager)
)
```
Above example, the @IsManager parameter is optional with a default value of NULL. When calling the function, you can choose to provide or omit this parameter. If omitted, the function will return all employees in the specified department regardless of their managerial status.