I had an interesting discussion with my office mate today about return values and the what is TRUE and FALSE. He was wondering what value to put as 'success' on a RETURN statement inside of a stored procedure.
My recommendation to him was to follow the @@ERROR convention of 0 (zero) being the value for success. He rightly asked if 0 did also mean 'false' by convention. That got me into a discussion of the BIT data type in SQL Server and the use of 0 or 1 to indicate true or false.
Well it turns out that SQL Server recoginizes two strings; 'TRUE' and 'FALSE' as valid values that can be assigned and used for comparison of BIT variables. Any other strings will cause a conversion error.
To illustrate the point see the code at the bottom of this entry. Why would you ever use the strings 'TRUE' and 'FALSE' versus 1 or 0?
The answer CLARITY!
Regards, Dave
--
--
declare @theBit bit
set @theBit = 'TRUE'
if @theBit = 'TRUE' print 'It is true'
if @theBit = 'FALSE' print 'It is false'
--
print '--Next case--'
set @theBit = 'FALSE'
if @theBit = 'TRUE' print 'It is true'
if @theBit = 'FALSE' print 'It is false'
--
--This will cause a conversion error
--
set @theBit = 'BLAH'
--
