OnTopic-Library/OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql at 2df59ad9e527839557aef432a9843a4a86046715 · OnTopicCMS/OnTopic-Library · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
--------------------------------------------------------------------------------------------------------------------------------
-- UPDATE RELATIONSHIPS
--------------------------------------------------------------------------------------------------------------------------------
-- Saves the n:n mappings for related topics.
--------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[UpdateRelationships]
	@TopicID		INT	= -1,
	@RelationshipKey	VARCHAR(255)	= 'related',
	@RelatedTopics		TopicList	READONLY
AS

--------------------------------------------------------------------------------------------------------------------------------
-- DECLARE AND SET VARIABLES

DECLARE	@Existing_TopicIDs	TopicList

--------------------------------------------------------------------------------------------------------------------------------
-- IDENTIFY EXISTING VALUES
--------------------------------------------------------------------------------------------------------------------------------
INSERT
INTO	@Existing_TopicIDs (
	  TopicID
	)
SELECT	Target_TopicID
FROM	Relationships
WHERE	Source_TopicID		= @TopicID
  AND	RelationshipKey		= @RelationshipKey

--------------------------------------------------------------------------------------------------------------------------------
-- INSERT NOVEL VALUES
--------------------------------------------------------------------------------------------------------------------------------
INSERT
INTO	Relationships (
	  Source_TopicID,
	  RelationshipKey,
	  Target_TopicID
	)
SELECT	@TopicId,
	@RelationshipKey,
	Target.TopicID
FROM	@RelatedTopics		Target
FULL JOIN	@Existing_TopicIDs	Existing
  ON	Existing.TopicID	= Target.TopicID
WHERE	Existing.TopicID	is null

--------------------------------------------------------------------------------------------------------------------------------
-- RETURN TOPIC ID
--------------------------------------------------------------------------------------------------------------------------------
RETURN	@TopicID;