I'm struggling with a T-SQL procedure and I am hoping you can help.
I need to know if
- A row exists in a table for a given ID
- 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:
- No case ever opened. This should return 0.
- A case has been previously opened, but is now closed. This too should return 0.
- An opened case exists. This should return 1.