What is the best way to return boolean logic from a UDF?

_{Saturday, March 18, 2006 12:30 PM | 1 vote}

CREATE FUNCTION MyBoolean(@p1 varchar(255),@p2 varchar(255) )

RETURNS bit
AS
BEGIN
   DECLARE @b bit
   SET @b=1 -- plus other heavy logic
   RETURN @b
END
GO

then call this in a CASE or IF-ELSE this:

IF MyBoolean('foo','bar')=1 THEN ...

Is there a better way to return and use boolean variable like other programming languages(like c# or vb, that have a true boolean data type?

_{Saturday, March 18, 2006 2:53 PM | 2 votes}

Hi,

a bit is only a subtype of integer which specific internal rules (only values are 0 and 1), this is the best way in SQL Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

_{Saturday, March 18, 2006 2:57 PM | 2 votes}

Nope.  This it the way.  There is not a boolean type in SQL that you can use (technically 1=1 returns a boolean TRUE, 1=0 returns Boolean False and 1=NULL returns Boolean UNKNOWN.) 

The problem lies in support of NULL as a value.  Then NULL as a value has to be treated differently than UNKNOWN four valued logic which is too difficult to deal with.  I wish we had one too, but bit is a common implementation, especially if the middleware can treat it as true or false.  Otherwise 'T' or 'F' as a char(1) is a good way to go as well.

_{Saturday, March 18, 2006 3:40 PM}

Thank you guys. That was quick.

I am not talking about middleware, nor about column definitions.

I am talking LOGICAL implementation of boolean return value. It can just be a block of code that sets a bit variable to be used down the line within the same entity. Or a UDF return value. 

For example UDF in a check constraint:

ALTER TABLE MyTable WITH CHECK
   ADD CONSTRAINT CK_MyTable_MyConstaint
      CHECK  ( MyBoolFunction ( MyTable.Col1)=1)

In the UDF I would have to implement default return value if Col1 is null.

This is not much different logically from c# boolean which is a value type and cannot be null, so often we are forced to decide whether to tack the "LOGICAL UNKNOWN"  to either the true or the false. Since the default is false it comes naturally but it does not have to be. Anyway, c#2 has the nullable types now. But my question is the opposite, best way to implement the NONNULLABLE boolean logic in SQL?

1.Avoiding null when using the function in IF ELSE is not hard by explicitly calling =1 or  =0 if the value is null the expression evaluates to false, right?

2.If I made sure there is a default bit value returned by the function, am I guaranteed it would not be NULL? So I wouldn't have to check again for null every time I access that function?

3.If explicitly covering both bit values, do I even need the ELSE clause?
What about the constraint example above, which is not an if-else construct

4.Correct me if I am wrong a constraint is just  "if true", right?

_{Sunday, March 19, 2006 6:47 PM | 2 votes}

ANSI SQL has a boolean data type. It is just that SQL Server and other RDBMSes doesn't implement it yet. So your only options are to use bit, tinyint, char(1), user-defined type (with rules/default), CLR type etc. As for implementing non-nullable type in SQL Server, there is really no way. You can specify NOT NULL as a constraint on columns and this is pretty much it. Variables are nullable by default, most of the built-in functions can return NULL due to overflow, invalid input etc.

1. NO. NULL <> true or NULL <> false or NULL <> any non-null value. So the predicate or condition evaluates to UNKNOWN. And it also depends on how the check is actually performed. CHECK constraints for example is evaluated as NOT FALSE so this means if the expression is NULL or true the CHECK constraint is satisfied. You have to basically look at three-valued logic in any of the db fundamentals book

2. I am not sure what you mean by default vit value returned by the function. Best is to change the RETURN @value to RETURN ISNULL(@value, 0) or RETURN COALESCE(@value, 0) so that you are protected by any operations in the UDF that might possibly set the value to NULL. But it really depends on the UDF logic

3. See my explanation on the CHECK constraint above

4. NO. See above.

_{Tuesday, March 21, 2006 8:34 AM}

Thanks Umachandar!

check constraints evaluated as Not False is very helpful. Yes I am aware of the functions that replace nulls with a default value.

Here is an example of a function(it could have just been a block of code) that evaluates a bool param and only checks for true false and ignores null.

Also from an assignment point ov view it does not return null.

CREATE FUNCTION MyBoolFunction (@boolParam bit)
RETURNS bit
AS
BEGIN
     DECLARE @boolVar bit
     
     IF (@boolParam = 0)      -- explicit check for a bool value
            SET boolVar =  0
     ELSE
         BEGIN
             IF (@boolParam = 1)    -- explicit check for a bool value
                 SET boolVar = 1  
             ELSE      --  not explicit check, default value assigned(instead of null)
                  SET boolVar = 1  
         END

     RETURN @boolVar
END

So am I not really implementing 2-valued bool logic here?