fdMYDs6A

1IF NOT EXISTS(SELECT *
2              FROM INFORMATION_SCHEMA.TABLES
3              WHERE TABLE_NAME = N'FacilityContacts')
4  BEGIN
5    CREATE TABLE [dbo].[FacilityContacts]
6    (
7      [FacilityID] [INT] NOT NULL,
8      [ContactID]  [INT] NOT NULL,
9      CONSTRAINT [FK_FacilityContacts_Facility]
10        FOREIGN KEY ([FacilityID]) REFERENCES [dbo].[Facility] ([ID]) ON DELETE CASCADE,
11      CONSTRAINT [FK_FacilityContacts_Contact]
12        FOREIGN KEY ([ContactID]) REFERENCES [dbo].[contact] ([ID]) ON DELETE CASCADE
13    )
14  END
15GO
16
17
18IF EXISTS(SELECT *
19          FROM sys.objects
20          WHERE object_id = OBJECT_ID(N'[usp_GetFacilityContactListById]')
21            AND type in (N'P', N'PC'))
22  DROP PROCEDURE [dbo].[usp_GetFacilityContactListById]
23GO
24CREATE PROCEDURE [dbo].usp_GetFacilityContactListById @ID INT
25AS
26BEGIN
27  SELECT *
28  FROM Contact c
29         LEFT JOIN [dbo].[FacilityContacts] fc
30                   ON fc.ContactID = c.Id
31  WHERE fc.FacilityID = @id
32END
33GO
34
35IF EXISTS(SELECT *
36          FROM sys.objects
37          WHERE object_id = OBJECT_ID(N'[usp_DeleteFromFacilityContacts]')
38            AND type in (N'P', N'PC'))
39  DROP PROCEDURE [dbo].[usp_DeleteFromFacilityContacts]
40GO
41CREATE PROCEDURE [dbo].usp_DeleteFromFacilityContacts @facilityID INT, @contactID INT
42AS
43BEGIN
44  DELETE
45  FROM [dbo].[FacilityContacts]
46  WHERE FacilityID = @facilityID
47    AND contactID = @contactID
48END
49GO
50
51IF EXISTS(SELECT *
52          FROM sys.objects
53          WHERE object_id = OBJECT_ID(N'[usp_AddToFacilityContacts]')
54            AND type in (N'P', N'PC'))
55  DROP PROCEDURE [dbo].[usp_AddToFacilityContacts]
56GO
57CREATE PROCEDURE [dbo].usp_AddToFacilityContacts @facilityID int, @contactID int
58AS
59IF NOT EXISTS
60  (SELECT 1
61   FROM [dbo].[FacilityContacts]
62   WHERE FacilityID = @facilityID
63     AND ContactID = @contactID
64  )
65  BEGIN
66    INSERT INTO [dbo].[FacilityContacts](FacilityID, ContactID)
67    VALUES (@facilityID,
68            @contactID)
69  END
70GO