An expression of non-boolean type specified in a context where a condition is expected, near 'Begin'

Question

I am working on a hotel project and I need to check the availability of rooms. Here the logic is first need to check the room availability if it is not available then I need to check if checkout date entered by the customer is equal to the checkout date of any customer:

ALTER PROCEDURE [dbo].[customerdetails] (@CheckIn     DATE, ...)
AS
  BEGIN
      BEGIN TRY
          IF ( (SELECT Available
                FROM   rooms
                WHERE  roomtype = @RoomType) > 0 )
            BEGIN
                INSERT INTO Customerdetail
                VALUES      (@CheckIn, ...)
            END
          ELSE IF(SELECT *
             FROM   Customerdetail
             WHERE  RoomType = @RoomType
                    AND CheckOut = @CheckOut)
            BEGIN
                INSERT INTO Customerdetail
                VALUES     (@CheckIn, ...)
            END
      END TRY

      BEGIN CATCH
          DECLARE @ErrMessage NVARCHAR(max)

          SET @ErrMessage=ERROR_MESSAGE()

          RAISERROR (@ErrMessage,16,1)
      END CATCH
  END 

But I get an error:

Msg 4145, Level 15, State 1
An expression of non-boolean type specified in a context where a condition is expected, near 'BEGIN'.

Answer 1

The problem is actually here, where you just say IF (get result):

ELSE IF(SELECT *
             FROM   Customerdetail
             WHERE  RoomType = @RoomType
                    AND CheckOut = @CheckOut)

This should be, I think, either IF (get result) = something or IF something (about result), e.g.:

ELSE IF EXISTS (SELECT *
             FROM   Customerdetail
             WHERE  RoomType = @RoomType
                    AND CheckOut = @CheckOut)

Also Paul is correct that this clause is not proper:

IF ( (SELECT Available
            FROM   rooms
            WHERE  roomtype = @RoomType) > 0 )

As written, if more than one row is returned, it will yield:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So, you should code this using EXISTS as he suggested.

You should also make sure you test this solution under a high level of concurrency, as Martin suggested in a comment.

Answer 2

Change:

IF ( (SELECT Available
            FROM   rooms
            WHERE  roomtype = @RoomType) > 0 )

to:

IF ( exists(SELECT Available
            FROM   rooms
            WHERE  roomtype = @RoomType) )