Is there a TSQL function that allows to validate an operation? Example:

Select (1 = 0) should return false while Select (1 = 1) should return true

or

Select ('a' = 'b') should return false while Select ('a' = 'a') should return true

Thanks in advance, and sorry for my bad English.

  • 7
    SQL Server doesn't have a boolean data type. You're really just trying to do select case when 1=1 then 1 else 0 end as logicaltest; ? – Philᵀᴹ Aug 9 at 14:31

You could use the BIT datatype to represent boolean data. A BIT field's value is either 1,0 or null.

In SQL you use 0 and 1 to set a bit field (just as a yes/no field in Access). In SQL Server Management Studio it displays as a True/False value (at least in recent versions).

When accessing the database through ASP.NET it will expose the field as a boolean value. Thanks @guffa, This answer from here

Example:

DECLARE 
    @strBoolean NVARCHAR(1000),
    @strSQL NVARCHAR(1000),
    @bResult BIT = 0

SET @strBoolean = N'(1=1)'     --returns 1, it means True
--SET @strBoolean = N'(1=0)'   --returns 0, it means False

SET @strSQL = N'SELECT @bCheck = 1 WHERE '+@strBoolean 
EXEC sp_executesql @strSQL, N'@bCheck INT OUT', @bResult OUT

SELECT 
    (CASE @bResult
          WHEN 1 THEN 'True'
          ELSE 'False'
     END)
     result

As already mentioned by Phil, there's no Boolean data type in SQL Server. So, we need the bigger picture of what you are trying to achieve in order to suggest an alternative.

Returning the string "false" is very different from returning a Boolean value of false (if SQL server had supported Boolean types in the first place), which is why we need a context.

There is not boolean data type in SQL Server. This means you can't return a bool as a column from a SELECT and you can't store a boolean value on a table. However, it's very common to use BIT or VARCHAR data types to represent boolean values. They are not boolean per se, you need to compare them against something to actually use them as a boolean.

With this in mind, you can't use a column directly as a filter:

DECLARE @BitValue BIT = 1 -- We assume that 1 represents "true" boolean value

SELECT 1 WHERE @BitValue
-- An expression of non-boolean type specified in a context where a condition is expected, near '@BitValue'.

You will have to compare the column against something to make the result a boolean the engine can use:

DECLARE @BitValue BIT = 1

SELECT 'OK!' WHERE @BitValue = 1 -- Boolean result
-- Result: OK!

This doesn't mean that SQL Server doesn't use boolean logic. These are needed whenever you write filtering or conditional expressions. These are a few:

  • WHERE and HAVING clauses must resort to true/false results:

    SELECT ... 
    WHERE Column = 'SomeValue' -- Boolean result
    
    SELECT ... 
    HAVING COUNT(1) = COUNT(DISTINCT(Column)) -- Boolean result
    
  • Inside CASE expressions true/false results determine which value to return:

    SELECT CASE 
        WHEN Column = 1 -- Boolean result
            THEN 'Value 1'
        WHEN Column <> 2 -- Boolean result
            THEN 'Value 2' END
    
    SELECT CASE Column
        WHEN 1 -- Boolean result (Column = 1)
            THEN 'Value 1'
        WHEN 2 -- Boolean result (Column = 2)
            THEN 'Value 2' END
    
  • IF statements conditionally executes blocks of statements:

    IF @Variable IS NOT NULL -- Boolean result
    BEGIN
    
        -- Statements
    
    END
    
  • IN expressions are just a syntactic sugar of OR operands:

    Column IN ('Value 1', 'Value 2', AnotherColumn) -- Boolean result
    
  • EXISTS and NOT EXISTS return true/false depending if the result set contains at least 1 row:

    EXISTS (SELECT ...) -- Boolean result
    

In most places where boolean results are needed, you can chain logical operators such as AND or OR.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.