T-SQL function that, when called, behaves as a boolean expression?

Question

I'm trying to write a T-SQL function that evaluates to a boolean.

I've read several articles stating that BIT is T-SQL equivalent of a BOOLEAN type (such as Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?, http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c3143a77-c921-40a7-920e-3d5c5f7a269a).

Yet, when I create function returning BIT:

create function dbo.fn_checkLength( @name nvarchar(50), @maxLength int) returns bit
as begin

    if len(@name) > @maxLength return cast(0 as bit);

    return cast(1 as bit);
end;
GO

It does not behave as a function that evaluates to a boolean, because the following statement:

create table dbo.test_table (
    id int,
    name nvarchar(50),

    constraint
        ck_test_table_maxLength
    check (
        dbo.fn_checkLength(name, 10)
    )
);
GO

Gives me:

Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

To make it work, when calling my function I have add a boolean operator in the mix, to really make the expression boolean:

create table dbo.test_table (
    id int,
    name nvarchar(50),

    constraint
        ck_test_table_maxLength
    check (
        dbo.fn_checkLength(name, 10) > 0
    )
);
GO

Why is this so? Is there any way to declare function that can be used in T-SQL statement as a regular boolean expression?

Thanks!

Typing `IF dbo.somefunc() = 1` isn't really that much harder than typing `IF dbo.somefunc()`, is it? Also if you want to act like this function returns a boolean you should say `= 1` not `> 0`. — Aaron Bertrand, Mar 29 '13 at 17:04
Checking for =0 and !=0 is the way I've done it. Not that other ways are "wrong", but that's what I do. I think Access(Jet) used to be "-1" and "0", thus I picked up this =0 or !=0 habit. — granadaCoder, Mar 29 '13 at 18:25
@Aaron, no, it's not much harder typing "=1", but makes errors when consuming the function more probable, that's all. The fact that you need to add boolean operator to make it work means that you can write the same check in many different ways (=1, !=0, <>0, >0...), so different coders using this function will use different coding styles to call it; Code will be hard to read and will cause bugs sometime in the future, once one coder misunderstands other coder's intention because of their different coding styles. I'd prefer a true bool function over the hack, but looks like I'm out of luck. — bora, Apr 01 '13 at 07:40

score 7 · Accepted Answer · answered Mar 29 '13 at 16:40

Before SQL:1999 SQL did not have a true Boolean data type, and few implementations currently support this feature.

The bit type is not a true Boolean type - it's a numeric type that can have the values 1 or 0 (or NULL).

So unless you use a SQL:1999 implementation that supports this feature you are stuck with having to have a Boolean expression in your check clause.

Brian · Answer 2 · 2013-03-29T19:54:34.620

1

Interesting.. I guess the only explanation is, CHECK by definition accepts only 'conditional expression that evaluates to TRUE or FALSE'. As per the second line in the documentation You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. 'Logical operators' is the key.

edited Mar 29 '13 at 19:54

answered Mar 29 '13 at 16:40

Brian

1,337
5
17
34

T-SQL function that, when called, behaves as a boolean expression?

2 Answers2