SQL Stored Procedure if statement with bit

Question

I have this if condition in my SQL Stored Procedure:

@isAlphabeticalSort bit = false

    if(@isAlphabeticalSort = false)
        ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
    Else
        ORDER BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER

but I get a few errors with this:

Invalid Column name false

Incorrect Syntax near 'ORDER'

I even tried adding the begin and end in between the if and the else and still I get the same errors....what am I doing wrong?

Answer 1

@isAlphabeticalSort bit = 0

Ref: https://msdn.microsoft.com/en-us/library/ms177603.aspx

You might also look at how your ORDER BY is written...

ORDER BY 
   CASE @isAlphabeticalSort 
      WHEN 0 THEN V_CONSTAT_ACTUAL_DATES.DATE_TO_END 
      ELSE V_CONSTAT_ACTUAL_DATES.JOB_NUMBER 
   END

Edit: You need to handle the conversion error(s) -- ex.:

ORDER BY 
   CASE @isAlphabeticalSort 
      WHEN 0 THEN CONVERT(VARCHAR(8),V_CONSTAT_ACTUAL_DATES.DATE_TO_END,112) 
      ELSE CONVERT(VARCHAR, V_CONSTAT_ACTUAL_DATES.JOB_NUMBER)
   END

Answer 2

TSQL recognizes bits as either 1 or 0.

So you can do:

@isAlphabeticalSort bit = 0

if(@isAlphabeticalSort = 0)
    ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
Else
    ORDER BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER

OR

@isAlphabeticalSort bit = 0

if(@isAlphabeticalSort = CAST('FALSE' AS BIT))
    ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
Else
    ORDER BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER

to check the bit value.

Edit:

All three of these statements are equivalent:

• if(@isAlphabeticalSort = 0)
• if(@isAlphabeticalSort = CAST('false' as bit))
• if(@isAlphabeticalSort = CONVERT(bit, 'false'))