# SQL stored procedure that returns a boolean value?

CREATE PROCEDURE dbo.foo
AS
BEGIN
DECLARE @true BIT, @false BIT;
SET @true = 1;
SET @false = 0;

IF (some condition)
Select @true;
ELSE
Select @false;
END


SQL is not the language that I'm strongest in now, but the above stored procedure seems to be produce the desired functioning: returning the truth value of some condition.

Is there a better way to define this stored procedure? By the way, it runs on MS SQL 2008.

-

Since SQL Server has no Boolean data type, and since the bit values 1 and 0 are widely used and understood to represent true and false in many programming languages, I would simply return 0x1 or 0x0 as appropriate. I don't think that actually declaring @true and @false variables adds anything to the code.

What might add something would be to return the value in an output parameter with a useful name:

CREATE PROCEDURE dbo.foo
@IsTrue BIT OUTPUT
AS
BEGIN

IF (condition)
SET @IsTrue = 0x1
ELSE
SET @IsTrue = 0x0
END


Especially if your procedure is called from other TSQL code, it is much easier to use an output parameter in the calling code than a result set. But if you're calling it from some other language then you can make that decision based on the calling language and framework. An output parameter is the preferred way to return scalar values from a procedure: use SELECT for returning result sets and RETURN for indicating the status of the procedure execution itself (succeeded, failed, failed with reason X etc.).

And by 'useful name' I mean a name that indicates that the purpose of the variable is to store a true/false condition. Names starting with Is or Has are usually good. But please do not use a double-negative name: something like if @IsNotEnabled = 0x0 is extremely difficult to parse mentally without interrupting your thoughts. IsEnabled = 0x1 is far better.

And depending on your condition, you might be able to use CASE:

SET @result = CASE WHEN (condition) THEN 0x1
ELSE 0x0
END


Whether or not that's preferable in this specific case is probably a question of taste, but CASE seems more 'SQL-like' to me. But IF and CASE do different things so you can't always substitute one for the other.

-