Why BIT value Returned from a function doesn't work in IF statement

• yyang4

  SSC Veteran

  Points: 235

  More actions

  January 20, 2019 at 10:00 am

  #379107

  I created a simple function that checks a given string and return a value of BIT type.  The script of this function is attached.  
  When I was trying to use this function in following IF statement, I got error of "an expression of non-boolean type specified in a context where a condition is expected".  The function clearly returns a BIT value, which is considered as boolean.  Why the IF statement failed?
  -------------------------------------------------------------------------
  IF (dbo.fn_IsQueryFilter(@InParam))
      PRINT 'has input'
  ELSE
      PRINT 'no input value'
  -------------------------------------------------------------------------
  I also did another test with following script.  I got exact same error shown above.  When did I do wrong in these two scripts?  
  DECLARE @yn bit
  SELECT @yn = 1
  IF (@yn)
   PRINT 'is true'

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 20, 2019 at 10:14 am

  #2019477

  You have to do it the long-winded way;

  If (condition) = 1
  Begin

  End

  ---

• Jeffrey Williams

  SSC Guru

  Points: 90346

  More actions

  January 20, 2019 at 10:39 am

  #2019481

  yyang4 - Sunday, January 20, 2019 10:00 AM

  I created a simple function that checks a given string and return a value of BIT type.  The script of this function is attached.  
  When I was trying to use this function in following IF statement, I got error of "an expression of non-boolean type specified in a context where a condition is expected".  The function clearly returns a BIT value, which is considered as boolean.  Why the IF statement failed?
  -------------------------------------------------------------------------
  IF (dbo.fn_IsQueryFilter(@InParam))
      PRINT 'has input'
  ELSE
      PRINT 'no input value'
  -------------------------------------------------------------------------
  I also did another test with following script.  I got exact same error shown above.  When did I do wrong in these two scripts?  
  DECLARE @yn bit
  SELECT @yn = 1
  IF (@yn)
   PRINT 'is true'

  There isn't a boolean data type in SQL Server and BIT is not boolean.  You need to specify the condition:

  IF dbo.fn_IsQueryFilter(@InParam) = 1
  PRINT 'true'
  ELSE
  PRINT 'false'

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs