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?

Chains · Accepted Answer · 2016-05-24 17:10:47Z

5

@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

edited May 24, 2016 at 17:10

answered May 24, 2016 at 14:49

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user979331 Over a year ago

this works when isAlphabeticalSort is 0, but when I get it to 1 I get this error when I run the stored procedure:

Conversion failed when converting date and/or time from character string. Warning: Null value is eliminated by an aggregate or other SET operation.

levelonehuman · Accepted Answer · 2016-05-24 14:51:01Z

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

answered May 24, 2016 at 14:51

levelonehuman

1,50515 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Stored Procedure if statement with bit

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related