T-SQL procedure giving a boolean result

Question

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.

Answer 1

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

Answer 2

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