SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tRelationContactContact](
	[RelationContactContactID] [int] IDENTITY(1,1) NOT NULL,
	[SourceContactID] [int] NULL,
	[DestinationContactID] [int] NULL
 CONSTRAINT [PK_PK_PersonInstitutionRelations] PRIMARY KEY CLUSTERED 
(
	[RelationContactContactID] ASC
)

WITH	MyResult(RelationContactContactID, SourceContactID, DestinationContactID, Hops)
AS
(
	-- First itteration
	SELECT	RelationContactContactID,
			SourceContactID, 
			DestinationContactID,
			1 Hops
	FROM	tRelationContactContact rcc

	UNION	ALL	

	-- Second itteration
	SELECT	rcc.RelationContactContactID, 
			rcc.SourceContactID, 
			rcc.DestinationContactID, 
			Hops + 1 Hops
	FROM	tRelationContactContact rcc,
			MyResult				myr
	WHERE	(	myr.SourceContactID			= rcc.SourceContactID
			OR	myr.DestinationContactID	= rcc.DestinationContactID
			OR	myr.SourceContactID			= rcc.DestinationContactID
			OR	myr.DestinationContactID	= rcc.SourceContactID
			)
	AND		myr.RelationContactContactID	<> rcc.RelationContactContactID
	AND		Hops < 2
)
SELECT	DISTINCT RelationContactContactID, SourceContactID, DestinationContactID
FROM	MyResult
WHERE	SourceContactID			= 543
OR		DestinationContactID	= 543
ORDER	BY
		RelationContactContactID, SourceContactID, DestinationContactID

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This Procedure gets all the Relations of a contact
-- Parameter: ContactID: The Key of the Contact to get the relations for.
-- Parameter: Hops:      How deep are we going into all of the relations.
ALTER	PROCEDURE	GetRelations (@ContactID int, @Hops int)
AS
BEGIN
	-- First get the Anchor part of the selection into a temporary table
	SELECT	RelationContactContactID, SourceContactID, DestinationContactID
	INTO	#tmp_RelationContactContact
	FROM	tRelationContactContact			rcc
	WHERE	(	rcc.SourceContactID				= @ContactID
			OR	rcc.DestinationContactID		= @ContactID
			)
	AND		rcc.DeletedOn						IS NULL

	WHILE	(@Hops > 1)
	BEGIN
		SET	@Hops = @Hops - 1

		-- Tell T-SQL that we would like to insert just using the identities of the fields.
		SET	IDENTITY_INSERT #tmp_RelationContactContact ON

		-- Here we get the Relations one step deeper and also insert them into the temp table
		INSERT	
		INTO	#tmp_RelationContactContact
		(	RelationContactContactID, SourceContactID, DestinationContactID	)
		SELECT	trc1.RelationContactContactID, trc1.SourceContactID, trc1.DestinationContactID
		FROM	
		(	-- This subselect will only contain those relations that are not in the temp table
			SELECT	RelationContactContactID, SourceContactID, DestinationContactID
			FROM	tRelationContactContact
			WHERE	DeletedOn				IS NULL

			EXCEPT

			SELECT	RelationContactContactID, SourceContactID, DestinationContactID
			FROM	#tmp_RelationContactContact
		)	trc1,
			#tmp_RelationContactContact	trc2
		WHERE	(	trc1.SourceContactID		= trc2.SourceContactID
				OR	trc1.DestinationContactID	= trc2.DestinationContactID
				OR	trc1.SourceContactID		= trc2.DestinationContactID
				OR	trc1.DestinationContactID	= trc2.SourceContactID
				)

		-- If nothing was inserted we can end the loop here.
		IF (@@ROWCOUNT = 0)
		BEGIN
			SET @Hops = 0
		END
	END
	
	-- Return all the rows that were inserted into the temp table
	SELECT	rcc.*
	FROM	#tmp_RelationContactContact	trcc,
			tRelationContactContact		rcc
	WHERE	trcc.RelationContactContactID = rcc.RelationContactContactID

	-- Delete the temp table
	DROP	TABLE	#tmp_RelationContactContact
END