How does one use WITH within a Scalar Function?

Question

I'm trying to add a WITH clause to a statement within a scalar function but when I do, I get the following syntax errors:

SQL80001: Incorrect syntax near 'WITH'

SQL80001: Incorrect syntax near ')'

The last error refers to the final closing bracket.

Here a greatly-simplified example of the SQL which also fails for the same reason:

CREATE FUNCTION IsSumEqualToTen
(
    @number1 INT,
    @number2 INT
)
RETURNS BIT
AS
BEGIN
    RETURN
    (
        WITH AddNumbers AS
        (
            SELECT @number1 + @number2
        )
        SELECT CASE WHEN AddNumbers = 10
        THEN 1
        ELSE 0
        END
    )
END

I know that when changed to this, it has no syntax errors:

CREATE FUNCTION IsSumEqualToTen
(
    @number1 INT,
    @number2 INT
)
RETURNS BIT
AS
BEGIN
    RETURN
    (
        CASE WHEN (@number1 + @number2) = 10
        THEN 1
        ELSE 0
        END
    )
END

But for what I'm actually doing I need a recursive CTE to flatten a heirarchy so the WITH is essential.

Why do you need a `WITH` at all here? What's wrong with `WHEN (@number1 + @number2)`? If you *actually* need an rCTE is sounds like you need an **inline** table-value function, not a multi-line scalar function. You should be showing us a realistic example of what you need, not an overly simplified one that doesn't demonstrate the *real* problem, as there is no need for a `WITH` here. — Thom A, Aug 20 '19 at 08:40
This is the problem I'm trying to solve: https://stackoverflow.com/questions/57494311/how-does-one-create-a-parameterized-recursive-cte-to-flatten-a-heirarchy-within/57494776?noredirect=1#comment101480837_57494776 — Matt Arnold, Aug 20 '19 at 08:42
So this is a duplicate of the other question, or an XY problem of it? — Thom A, Aug 20 '19 at 08:43
Why do you want to use a Scalar funtion here anyway? Unless you're using SQL Server 2019's scalar inlining functionality, you should really stick to inline table-value functions. — Thom A, Aug 20 '19 at 08:44
It's an XY; I've reached the point where I've established the remaining issue is using the `WITH` within a scalar function. I need the scalar function as I'm returning a `bool` to C# to determine whether to continue to execute some other logic to go and get the desired data. If I had designed this part of the system myself, I would have moved the logic in question into a stored procedure which encompasses this logic as well. — Matt Arnold, Aug 20 '19 at 08:49
I thin the problem here is you've gone down the wrong rabbit hole, and now you're trying to fix problems that aren't really relevant. SQL Server doesn't have a bool data type, so you can't return one to C#. Using a Scalar function is definately the wrong approach here. — Thom A, Aug 20 '19 at 08:52
`BIT` is SQL's equivalent to `bool`; I'm using EntityFrameworkExtras.EF6 to call Scalar functions. — Matt Arnold, Aug 20 '19 at 08:55
No, it isn't. if it were, then `SELECT * FROM (VALUES('a'),('b'))V(c) WHERE CAST(1 as bit);` would work. — Thom A, Aug 20 '19 at 08:57
I don't know why SQL doesn't treat a `BIT` as a `bool` but I do know it's either `1` or `0` and thus translatable to `true` or `false` without any potential for loss-of-data. Also, I was mistaken, I'm not using that library to call Scalar functions; Entity Framework allows them to be called via "Function Imports" in the Model Browser for the EDMX. All I'm trying to fix here is an SQL syntax error; EF has no problem with scalar functions returning a `BIT` and translating it into a `bool`. — Matt Arnold, Aug 20 '19 at 09:44
*"I don't know why SQL doesn't treat a `BIT` as a `bool`"* Because a `bit` and a `Bool` are completely different datatypes. — Thom A, Aug 20 '19 at 09:45
For the exact reason i showed you above. `1` is not a boolean result in SQL Server. — Thom A, Aug 20 '19 at 09:48

score 1 · Accepted Answer · answered Aug 20 '19 at 10:08

First, I must say I agree with Larnu on this one - using a scalar function is probably not the best course of action you can take.
However, I wanted to show you that there's no problem using a common table expression in a user defined function - table valued or scalar.

The problem with the code you've shown is that you do have some syntax errors as well as some conceptual errors. A fixed version of that function (still using a cte) is this:

CREATE FUNCTION IsSumEqualToTen
(
    @number1 INT,
    @number2 INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @Result bit;

        WITH AddNumbers(AddNumbers) AS
        (
            SELECT @number1 + @number2 
        )
        SELECT @Result = CASE WHEN MAX(AddNumbers) = 10
            THEN 1
            ELSE 0
            END
        FROM AddNumberes;

    RETURN @Result;
END

Notes:

Each column in the cte result set must be named.
A cte is treated as a table, you must take into consideration the fact that it might contain more than one row (even if it can only contain one, the compiler can't know that).
scalar functions are not inlined, this means that you don't do return (body here) but body...; return scalarValue. Infact, this is how the official documentation looks like:

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ = default ] [ READONLY ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]

How does one use WITH within a Scalar Function?

1 Answers1