Is it possible for a function to return rows in some cases and a scalar value in other cases?

Question

I was recently posed a problem by a student based on assignment instructions they'd gotten elsewhere, and I don't think the requested result is possible. Perhaps the question was poorly worded. See what you think.....

The instructions were to create a FUNCTION (Notably, NOT a stored proc) that returns rows based on a parameter that would be plugged into a WHERE clause. (Here's the goofy part). The instructions also said "If the result is null, return a zero."

My major problems with this:

• The result of a TVF (inline or multi-valued) would never to my knowledge return NULL. An empty result set maybe, but not null.
• We could still phony this up somehow using Stored Proc return values, but the instructions pointedly requested a function.
• Even if we somehow COULD return a zero from a TVF, what happens when we're using the TVF as a data source in a query? (Either alone or joined to another table's result set). We'd be... what? Joining a scalar value to a result set?!?

Is there some new feature of T-SQL that I missed that would make this possible? Cuz I'm just not seeing it.

(With a stored proc, we could do this)

CREATE PROCEDURE EmployeeDetails
(@empID int)
AS

declare @rows int;

SELECT @rows= count(*) from Employees
 where EmployeeID=@empID;

if @rows=0
 RETURN 0;
else
 SELECT * from Employees where EmployeeID=@empID;
 RETURN @@rowcount;
go

(But we're still getting either a full result set and a row count for the return value for a valid EmployeeID, or just a return value for an invalidEmployeeID)

(For an actual TVF, this is the best I could do, which is still going to result in problems, problems that would be even further exacerbated when there were more fields in the results that would potentially need to be joined to other PK/FK combos)

CREATE FUNCTION dbo.EmployeeWithNullTest 
(
    @empID int
)
RETURNS 
@outputTable TABLE 
(
     employeeid int
    ,lastname varchar(20)
    ,firstname varchar(20)
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    insert into @outputTable
    select employeeid, firstname, lastname 
    from employees
    where employeeid=@empID

    if (select count(*) from @outputTable)=0
    insert into @outputTable  values (0,0,0)

    RETURN 
END
GO

Answer 1

Yes this is possible using an inline table-valued function (or a regular query for that matter. You can do it in a single statement, so a multi-statement TVF is not necessary.

We just start with a single-row VALUES constructor as a driving row, and left-join anything else to it. Note that all WHERE conditions must be moved to the ON.

CREATE OR ALTER FUNCTION dbo.EmployeeWithNullTest 
(
    @empID int
)
RETURNS TABLE
AS RETURN
SELECT
     employeeid = ISNULL(e.employeeid, 0)
    ,lastname   = ISNULL(e.lastname, '')
    ,firstname  = ISNULL(e.firstname, '')
FROM (VALUES(0)) v(dummy)
LEFT JOIN employees e ON e.employeeid = @empID;

Having said that, it is rare that you would actually want to do this, because you could instead just use OUTER APPLY to call the function. That acts similar to a left-join, so you can just ISNULL it on the outside.

Answer 2

"Is there some new feature of T-SQL that I missed that would make this possible? Cuz I'm just not seeing it."

What you stated is correct. TVF only returns a table. You could do it with a procedure, but that would not meet the "odd" requirements