Skip to the content

Hoe maak ik dynamisch een JOIN tussen tabellen sql server

Het genereren van een dynamische query is een leuke klus maar wat nu als ik wel de tabellen beschikbaar heb maar niet direct de velden die een relatie bepalen.

Deze vergelijking maken in de eerste normaalvorm is niet zo heel erg lastig maar indien je werkt met verschillende niveaus wordt het flink lastig want hoe bepaal je dynamisch de relaties tussen de verschillende tabellen.

Uitgangspunten

Tijdens het maken van een API codegenerator, waarmee modellen en controllers kunnen worden gegenereerd, kwam deze vraag naar boven. Hoe kan ik zonder te hoeven programmeren toch een select query laten maken door een gebruiker. De gebruiker koppelt een property aan een tabel en veldnaam (dit ter extra info)

Voor dit blog heb ik de AdventureWorks2016 database gebruikt. Dit kan iedere andere relationele database zijn.

Tevens maak ik gebruik van de functie STRING_SPLIT. Deze functie splitst een string in verschillende rijen. Vergelijk de functie maar met de Array in .NET. Deze functie is beschikbaar vanaf SQL Server 2016. Mocht je een lagere versie hebben stuur dan even een bericht want ik heb een eigen split functie beschikbaar die ik je graag door mail.

Dynamisch een FROM statement genereren

Ik wil dynamisch een query genereren waarbij ik alleen beschik over de veldnamen en de tabelnamen. Het resultaat moet gelijk zijn aan het voorbeeld hieronder.

SELECT SP.BusinessEntityID, SOH.CustomerID, SUM(SOD.LineTotal) AS Expr1

FROM Sales.SalesOrderDetail AS SOD INNER JOIN

Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID INNER JOIN

Sales.SalesPerson AS SP ON SOH.SalesPersonID = SP.BusinessEntityID

GROUP BY SP.BusinessEntityID, SOH.CustomerID

 

De gegevens waarover ik beschik zijn;

 

SalesOrderDetail LineTotal

SalesOrderHeader CustomerID

SalesPerson BusinessEntityID

 

Hoe bepaal ik nu dynamisch de relaties tussen deze gegevens.

Eerst zorg ik ervoor dat de tabelnamen in een string worden geplaatst. Als je een tabel meegeeft waarin de tabelnamen staan kan dat natuurlijk ook immers we doen met de split functie exact hetzelfde.

DECLARE @TABLES VARCHAR(1000)

SET @TABLES = 'SalesOrderDetail,SalesOrderHeader,SalesPerson'

Vervolgens maak ik 2 tijdelijke tabellen. In de eerste tabel slaan we alle mogelijke relaties van de meegegeven tabellen op en in de tweede tabel slaan we de uiteindelijke relaties op.

 

                DECLARE @BUFFER TABLE

                (

                               TableName VARCHAR(100) NULL,

                               FieldName VARCHAR(100) NULL,

                               RelatedTableName VARCHAR(100) NULL,

                               RelatedFieldName VARCHAR(100) NULL

                )

 

                DECLARE @RELATIONS TABLE

                (

                               TableName VARCHAR(100) NULL,

                               FieldName VARCHAR(100) NULL,

                               RelatedTableName VARCHAR(100) NULL,

                               RelatedFieldName VARCHAR(100) NULL

                )

 

Nu plaats ik alle relaties van de drie tabellen op in de tabel @BUFFER. Als je een tabel met tabelnamen meegeeft in plaats van een string met tabelnamen vergeet dan niet om de JOIN STRING_SPLIT aan te passen naar je eigen tabel.

 

--Haal alle mogelijke relaties op tussen de tabellen in de route

INSERT INTO @BUFFER(TableName, FieldName, RelatedTableName, RelatedFieldName)

SELECT

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

INNER JOIN STRING_SPLIT(@TABLES, ',') s ON OBJECT_NAME(f.parent_object_id) = s.value

 

Deze actie resulteert in de onderstaande output waarbij je ziet dat alle relaties van de drie tabellen zijn opgenomen in de tabel.

 selecteer alle relaties tussen tabellen

Vervolgens lus ik de output met een cursor om de simpele reden dat de cursor me de meeste vrijheid geeft om data te manipuleren of er vervolgacties mee uit te zetten. Wees terughoudend in het gebruik van de cursor want verkeerd gebruik kan leiden tot eindeloze lussen.

Terug naar de lus, wanneer een gerelateerde tabelnaam voorkomt in de @BUFFER tabel als TableName dan is er een match en maakt de regel deel uit van de relaties die we zoeken.

Dit record voegen we dan ook toe aan de @RELATIONS tabel.

Lus klaar is een keurig overzicht van de relaties waarnaar je op zoek bent en waarmee je het volledige FROM statement kan genereren.

de relaties tussen tabellen dynamisch bepaald

Have Fun!

Hieronder de volledige code

 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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
	DECLARE @TABLES VARCHAR(1000)
	SET @TABLES = 'SalesOrderDetail,SalesOrderHeader,SalesPerson'

	--Eigen functie split kan vanaf SQL SERVER 2016 worden vervangen door STRING_SPLIT functie 
	
	DECLARE @BUFFER TABLE
	(
		TableName VARCHAR(100) NULL,
		FieldName VARCHAR(100) NULL,
		RelatedTableName VARCHAR(100) NULL,
		RelatedFieldName VARCHAR(100) NULL
	)

	DECLARE @RELATIONS TABLE
	(
		TableName VARCHAR(100) NULL,
		FieldName VARCHAR(100) NULL,
		RelatedTableName VARCHAR(100) NULL,
		RelatedFieldName VARCHAR(100) NULL
	)
	--Haal alle mogelijke relaties op tussen de tabellen in de route
	INSERT INTO @BUFFER(TableName, FieldName, RelatedTableName, RelatedFieldName)
	SELECT 
		OBJECT_NAME(f.parent_object_id) AS TableName,
		COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
		OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
		COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
	FROM sys.foreign_keys AS f
		INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
		INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
		INNER JOIN STRING_SPLIT(@TABLES, ',') s ON OBJECT_NAME(f.parent_object_id) = s.value

	SELECT * FROM @BUFFER
	DECLARE
		@TABLE VARCHAR(100),
		@FIELD VARCHAR(100),
		@RELATEDTABLE VARCHAR(100),
		@RELATEDFIELD VARCHAR(100)

	DECLARE FindRelation_Cursor CURSOR FOR
	SELECT 
		TableName, FieldName, RelatedTableName, RelatedFieldName
	FROM @BUFFER

	OPEN FindRelation_Cursor
	FETCH NEXT FROM FindRelation_Cursor 
	INTO @TABLE, @FIELD, @RELATEDTABLE, @RELATEDFIELD

	WHILE @@FETCH_STATUS = 0 
	BEGIN

		IF @RELATEDTABLE IN (SELECT TableName FROM @BUFFER) 
		BEGIN
			INSERT INTO @RELATIONS(TableName, FieldName, RelatedTableName, RelatedFieldName)
			VALUES(@TABLE, @FIELD, @RELATEDTABLE, @RELATEDFIELD)
		END

		FETCH NEXT FROM FindRelation_Cursor 
		INTO @TABLE, @FIELD, @RELATEDTABLE, @RELATEDFIELD
	END
	CLOSE FindRelation_Cursor
	DEALLOCATE FindRelation_Cursor

	SELECT * FROM @RELATIONS

Incidenten registratie

In iedere organisatie worden fouten gemaakt en vinden incidenten plaats. Om hier inzicht in te krijgen en de kans op herhaling te voorkomen, is het van belang incidenten op de juiste manier te registreren. Bekijk de voordelen van ons incidenten registratiesysteem.

Incidenten registratie

"Wie op zoek is naar een teamplayer, een man van zijn woord en een uitmuntende programmeur, dan kan ik Reinder zeker aanbevelen. Reinder bedankt en tot de volgende uitdaging."

Marijn Snoek - Fleetaccess