views:

2353

answers:

6

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.
+3  A: 

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
Jhonny D. Cano -Leftware-
I get an Error 156: Incorrect Syntax near the keyword 'BEGIN'.Incorrect Syntax near the keyword 'THEN'.
graham.reeds
Check again, I corrected, changed IF Syntax for CASE Syntax
Jhonny D. Cano -Leftware-
However, check Edoode's answer, which is also very good
Jhonny D. Cano -Leftware-
Okay. This fails (like Edoode's) on check 2 I perform (listed in the edit question).
graham.reeds
I've just edited the answer, it was just a matter of changing the order of the result
Jhonny D. Cano -Leftware-
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.
DBAndrew
Yeah, I stated later when I read Edoode's answer
Jhonny D. Cano -Leftware-
+3  A: 

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
Chris Shaffer
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.
graham.reeds
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.
Chris Shaffer
Yours was the one that worked straight out the box, so you get the tick even if it isn't the easiest to understand.
graham.reeds
+1  A: 

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
edosoft
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.
graham.reeds
@graham. With some sample data I could be of assistance
edosoft
+1  A: 

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
Charles Bretana
+2  A: 

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
JoshBerke
A: 
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
Muhammad Haseeb Asif
This doesn't do what I needed.
graham.reeds