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

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

• rfarmer768

  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
  ---
  

• Bruce VB111480

  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.

  
  
  

• Recrehal

  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

   

  
  

• trident

  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.

  
  
  

• Blair Hall

  Thank you guys. That was quick.

  Louis Davidson - SQL Server MVP wrote:

  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.

  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.

  Louis Davidson - SQL Server MVP wrote:

  The problem lies in support of NULL as a value.

  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

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