T-SQL procedure giving a boolean result

I'm struggling with a T-SQL procedure and I am hoping you can help.

I need to know if

1. A row exists in a table for a given ID
2. If one (or more) does exist then the latest one has another ID set to 5.

So the first table we need to get the row out of has two relevant ID's: The CaseID and LocationID, these are both integers. The second table has 1 relevant ID called StateID.

Currently I can get whether the row exists in a table part but soon as I try to do anything Enterprise Manager gives a syntax error before the END statement.

CREATE PROCEDURE [dbo].[HasActiveCase] 
(
    @LocationID INTEGER
)

AS

DECLARE @CaseID AS INTEGER
SELECT @CaseID=CaseID FROM dbo.Cases WHERE @LocationID=LocationID

SELECT CASE WHEN 
    @CaseID IS NULL 
THEN 
    0
ELSE  
    -- do something here to check CaseEvents.StateID is not 5 (closed)
END
GO

There probably is a way of getting what I need in a JOIN or something but I am a complete newbie here.

What would be the easiest (to understand) way of checking the StateID is not 5 and return the result as a true/false? (I know SQLServer doesn't have a boolean type but does have a Bit type instead.)

Also on a matter of style: The values in the ID's have a text-field associated with them - CaseEvents.StateID has the text 'Closed' for instance. Should I return values as the ID's and then replace the ID in code or return the objects with the ID's already replaced with the text? There will never be more than 20 or 30 results returned in a set and the table will never be very large as it's taken 5 years to get 2000 results in it.

NOTE: Can't use linq (or anything else .NETty) because this will be called from an VB6 program.

Update:

Only 1 case can be open at a time so only the most recent item would be of relevance.

The possible situations to have is:

1. No case ever opened. This should return 0.
2. A case has been previously opened, but is now closed. This too should return 0.
3. An opened case exists. This should return 1.

From stackoverflow

• Check if this works for you. Edited

  CREATE PROCEDURE [dbo].[HasActiveCase] 
  (
      @LocationID INTEGER
  )
  
  AS BEGIN
      DECLARE @CaseID AS INTEGER
      SELECT @CaseID = CaseID FROM dbo.Cases WHERE @LocationID=LocationID
  
      SELECT CASE WHEN 
       @CaseID IS NULL 
      THEN 0
      ELSE CASE WHEN (SELECT COUNT(*) FROM CaseEvents WHERE StateID <> 5) > 0 THEN 0 ELSE 1 END
      END
  END
  GO
  

  graham.reeds : I get an Error 156: Incorrect Syntax near the keyword 'BEGIN'.Incorrect Syntax near the keyword 'THEN'.

  Jhonny D. Cano -Leftware- : Check again, I corrected, changed IF Syntax for CASE Syntax

  Jhonny D. Cano -Leftware- : However, check Edoode's answer, which is also very good

  graham.reeds : Okay. This fails (like Edoode's) on check 2 I perform (listed in the edit question).

  Jhonny D. Cano -Leftware- : I've just edited the answer, it was just a matter of changing the order of the result

  DBAndrew : Using the IF EXISTS() method is more performance friendly as it will stop scanning the table when it finds the first instance that is true.

  Jhonny D. Cano -Leftware- : Yeah, I stated later when I read Edoode's answer

• I think this query will do what you are looking for; Note that your existing query has a bug in that if more than one case exists it will only check if whatever case happened to be selected by the initial query is closed (of course that is only true if it is possible to have more than one Case assigned to a particular location).

  SELECT @CaseID = dbo.Cases.CaseID
  FROM dbo.Cases
      JOIN dbo.CaseEvents ON dbo.Cases.CaseEventID = dbo.CaseEvents.CaseEventID
  WHERE @LocationID = dbo.Cases.LocationID
      AND 5 != dbo.CaseEvents.StateID
  
  SELECT CASE WHEN @CaseID IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS CaseExists
  

  graham.reeds : In my case you can only have 1 case open at at time so checking if the last item is a closure will suffice in this instance. I should of noted this in the problem description.

  Chris Shaffer : Sorry - I guess I should have been more specific when I said "check if the last case is closed" - This is not necessarily the most recent, the order is not going to be specific unless you explicitly include an ORDER BY clause.

  graham.reeds : Yours was the one that worked straight out the box, so you get the tick even if it isn't the easiest to understand.

• This might work as well:

  CREATE PROCEDURE [dbo].[HasActiveCase] 
  (
      @LocationID INTEGER
  )
  
  AS 
      IF EXISTS (SELECT CaseID FROM dbo.Cases WHERE @LocationID=LocationID)
      BEGIN
          IF EXISTS (SELECT * FROM CaseEvents WHERE StateID <> 5)
       SELECT 1 ELSE SELECT 0
      END
      ELSE
      SELECT 0
  
  GO
  

  graham.reeds : For some reason this fails on one of the two cases when data does exist. Case 1 is no case ever opened - this works. Case 2 is case has been previously opened, but is now closed - this fails returning 1. Case 3 is where an opened case exists - this passes returning 1.

  edosoft : @graham. With some sample data I could be of assistance

• Try this:

    Select Case When Exists 
        (Select * From CaseEvents
         Where CaseId =
             (Select CaseID From Cases 
              Where LocationId = @Location)
         And StateId = 5)  -- Or <> 5 I'm not sure which you want here
        Then 1 Else 0 End
  

• Lots of ways to solve this, here's one:

  CREATE PROCEDURE [dbo].[HasActiveCase] 
  (
      @LocationID INTEGER
  )
  
  AS
  
  DECLARE @CaseID AS INTEGER
  SELECT @CaseID=CaseID FROM dbo.Cases WHERE @LocationID=LocationID
  
  if (@CaseId IS NULL)
  BEGIN
      SELECT 0
  END
  ELSE if EXISTS ( SELECT * FROM CaseEvents WHERE StatusId=5 and CaseId=@CaseId)
  BEGIN
      SELECT 1
  END
  ELSE
  BEGIN
   SELECT 0
  END
  GO
  

• create procedure abc (id int)
  as
  declare @count int
  begin
  select @count=count(anycolumn) from table
  where id=@id
  if @count>0
  return 1
  else 
  return 0
  end
  

  graham.reeds : This doesn't do what I needed.