r4updb0d

1CREATE PROCEDURE [dbo].[HasActiveCase] 
2(
3    @LocationID	INTEGER
4)
5
6AS
7
8DECLARE @CaseID AS INTEGER
9SELECT @CaseID=CaseID FROM dbo.Cases WHERE @LocationID=LocationID
10
11SELECT CASE WHEN 
12    @CaseID IS NULL 
13THEN 
14    0
15ELSE  
16    -- do something here to check CaseEvents.StateID is not 5 (closed)
17END
18GO
19	
20SELECT @CaseID = dbo.Cases.CaseID
21FROM dbo.Cases
22    JOIN dbo.CaseEvents ON dbo.Cases.CaseEventID = dbo.CaseEvents.CaseEventID
23WHERE @LocationID = dbo.Cases.LocationID
24    AND 5 != dbo.CaseEvents.StateID
25
26SELECT CASE WHEN @CaseID IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS CaseExists
27	
28CREATE PROCEDURE [dbo].[HasActiveCase] 
29(
30    @LocationID INTEGER
31)
32
33AS BEGIN
34    DECLARE @CaseID AS INTEGER
35    SELECT @CaseID = CaseID FROM dbo.Cases WHERE @LocationID=LocationID
36
37    SELECT CASE WHEN 
38    	@CaseID IS NULL 
39    THEN 0
40    ELSE CASE WHEN (SELECT COUNT(*) FROM CaseEvents WHERE StateID <> 5) > 0 THEN 0 ELSE 1 END
41    END
42END
43GO
44	
45CREATE PROCEDURE [dbo].[HasActiveCase] 
46(
47    @LocationID	INTEGER
48)
49
50AS
51
52DECLARE @CaseID AS INTEGER
53SELECT @CaseID=CaseID FROM dbo.Cases WHERE @LocationID=LocationID
54
55if (@CaseId IS NULL)
56BEGIN
57    SELECT 0
58END
59ELSE if EXISTS ( SELECT * FROM CaseEvents WHERE StatusId=5 and CaseId=@CaseId)
60BEGIN
61    SELECT 1
62END
63ELSE
64BEGIN
65	SELECT 0
66END
67GO
68	
69CREATE PROCEDURE [dbo].[HasActiveCase] 
70(
71    @LocationID INTEGER
72)
73
74AS 
75    IF EXISTS (SELECT CaseID FROM dbo.Cases WHERE @LocationID=LocationID)
76    BEGIN
77        IF EXISTS (SELECT * FROM CaseEvents WHERE StateID <> 5)
78    	SELECT 1 ELSE SELECT 0
79    END
80    ELSE
81    SELECT 0
82
83GO
84	
85Select Case When Exists 
86      (Select * From CaseEvents
87       Where CaseId =
88           (Select CaseID From Cases 
89            Where LocationId = @Location)
90       And StateId = 5)  -- Or <> 5 I'm not sure which you want here
91      Then 1 Else 0 End
92	
93create procedure abc (id int)
94as
95declare @count int
96begin
97select @count=count(anycolumn) from table
98where id=@id
99if @count>0
100return 1
101else 
102return 0
103end