none
Problem beim Verwenden eines Parameters in einer dynamischen Abfrage in MS-SQL-Server2017 RRS feed

  • Frage

  • Hallo zusammen,

    ich habe eine Tabelle, in der die DISTINCT-Werte verschiedener Spalten weiter ausgewertet werden sollen (Pivot).

    Nun möchte ich den Spaltennamen als Parameter an die dynamische Abfrage übergeben:

    DECLARE @queryKriterium AS NVARCHAR(max),
    	@colKriterium AS NVARCHAR(100),
    	@cols AS NVARCHAR(max)
    
    Set @colKriterium = 'Ort'
    
    SET @queryKriterium =
    N'
    SELECT @cols = 
    STUFF
    (
      (
        SELECT '','' + QUOTENAME(' + @colKriterium + ')
        FROM HitTable$
        GROUP BY ' + @colKriterium + '
        FOR XML PATH(''''), TYPE
      ).value(''.'', ''NVARCHAR(max)''),1,1,''''
    )
    '
    execute (@queryKriterium)
    Print @cols

    Die Übergabe des Parameters '@colKriterium' funktioniert, die query wird auch fehlerfrei generiert, nur im Parameter @cols werden keine Werte ermittelt. Statt dessen bekomme ich bei der Print-Anweisung die Fehlermeldung:
    "Die @cols-Skalarvariable muss deklariert werden."

    In der Spalte 'Ort' stehen diverse Werte 'A','B','C' … Deshalb hier die dynamische Abfrage. Ohne Einbettung der @cols in die 'SET @queryKriterium = ...' liefert die Abfrage 'SELECT @cols = …' die gewünschten Werte.

    Weiß hier jemand, wie man das Problem lösen kann?

    Gruß Jürgen

    Dienstag, 18. September 2018 10:34

Antworten

  • also quasi so etwas...

    DECLARE @queryKriterium AS NVARCHAR(MAX),
            @colKriterium   AS NVARCHAR(100),
            @cols           AS NVARCHAR(MAX);
    
    SET @colKriterium = 'Ort';
    
    SET @queryKriterium = N'
    SELECT @cols = 
    STUFF
    (
      (	  
        SELECT '','' + QUOTENAME(' + @colKriterium + ')
        FROM HitTable$
        GROUP BY ' + @colKriterium + '
        FOR XML PATH(''''), TYPE
      ).value(''.'', ''NVARCHAR(max)''),1,1,''''
    )
    ';
    DECLARE @p NVARCHAR(MAX) = '@cols nvarchar(max) output';
    
    EXECUTE sp_executesql @queryKriterium, @p, @cols = @cols OUTPUT;
    
    PRINT @cols;

    Grüße Volker

    • Als Antwort vorgeschlagen Uwe RickenMVP Dienstag, 18. September 2018 13:58
    • Als Antwort markiert Jürgen Sch Mittwoch, 19. September 2018 08:44
    Dienstag, 18. September 2018 13:51

Alle Antworten

  • Hallo Jürgen,

    @cols ist innerhalt deines dynamischen SQLs nicht sichtbar. Nutze

    EXECUTE sp_execute

    und erstelle eine Parameterdefinition für die IN/OUT Parameter.

    Grüße Volker

    Dienstag, 18. September 2018 13:20
  • also quasi so etwas...

    DECLARE @queryKriterium AS NVARCHAR(MAX),
            @colKriterium   AS NVARCHAR(100),
            @cols           AS NVARCHAR(MAX);
    
    SET @colKriterium = 'Ort';
    
    SET @queryKriterium = N'
    SELECT @cols = 
    STUFF
    (
      (	  
        SELECT '','' + QUOTENAME(' + @colKriterium + ')
        FROM HitTable$
        GROUP BY ' + @colKriterium + '
        FOR XML PATH(''''), TYPE
      ).value(''.'', ''NVARCHAR(max)''),1,1,''''
    )
    ';
    DECLARE @p NVARCHAR(MAX) = '@cols nvarchar(max) output';
    
    EXECUTE sp_executesql @queryKriterium, @p, @cols = @cols OUTPUT;
    
    PRINT @cols;

    Grüße Volker

    • Als Antwort vorgeschlagen Uwe RickenMVP Dienstag, 18. September 2018 13:58
    • Als Antwort markiert Jürgen Sch Mittwoch, 19. September 2018 08:44
    Dienstag, 18. September 2018 13:51
  • Hallo Volker,

    danke für deine Antwort.

    Das ist genau, was ich gesucht habe. :-)

    Ich habe noch eine ergänzende Frage:

    Der Parameter '@colKriterium' wird hier noch fest gesetzt. Dieser Parameter soll jedoch von der UI-View übergeben werden. 

    Wie müsste ich die obige Abfrage dann umbauen? Evtl. als Function und wie würde das dann aussehen???

    Gruß Jürgen

    Mittwoch, 19. September 2018 04:15
  • Hallo Jürgen,

    in eine Funktion kannst du das so nicht einbauen, da "exec" darin nicht zugelassen ist. Das musst du dann "zweistufig" machen. Zunächst die Spalten abfragen und diese dann in deiner View verwenden.

    Grüße Volker

    Mittwoch, 19. September 2018 07:03
  • Hallo Volker,

    danke für den Hinweis, dass EXEC in FUNC/PROC nicht zugelassen ist.

    Da stehe ich dann vor einem Dilemma. Der Ergebnis-Parameter wird in einer nachfolgenden EXEC für eine Pivot-Table genutzt.

    Ich hatte schon eine "StoredProcedure' mit Parameter @result NVARCHAR(max) OUTPUT erzeugt und habe mich gewundert, dass innerhalb dieser PROCEDURE der Parameter nicht mit 'SELECT @result = ...' nicht gesetzt werden kann.

    Hier mal die komplette Anweisung:

    ------- Prüfen, ob Procedure vorhanden ist ----------
    IF OBJECT_ID('GetHitlisteByKriteriumCol', 'P') IS NOT NULL
        DROP PROCEDURE GetHitlisteByKriteriumCol
    GO
    -----------------------------------------------------
    Create PROCEDURE GetHitlisteByKriteriumCol
    (
    	@colKriterium AS NVARCHAR(100),
    	@result NVARCHAR(MAX) OUTPUT
    ) AS
    BEGIN
    SELECT @result =
    (
    	DECLARE @queryKriterium AS NVARCHAR(max),
    			@queryHitliste AS NVARCHAR(max),
    			@cols AS NVARCHAR(max)
    	SET @queryKriterium = N'
    	SELECT @cols = 
    	STUFF
    	(
    		(
    			SELECT '','' + QUOTENAME(' + @colKriterium + ') 
    			FROM HitTable$
    			GROUP BY ' + @colKriterium + '
    			FOR XML PATH(''''), TYPE
    		).value(''.'', ''NVARCHAR(max)''),1,1,''''
    	)
    	';
    	DECLARE @p NVARCHAR(MAX) = '@cols nvarchar(max) output';
    
    	EXECUTE sp_executesql @queryKriterium, @p, @cols = @cols OUTPUT;
    
    	SET @queryHitliste =
    	'
    	WITH subUnion AS
    	(
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = ''' + '0' + ''' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = ''' + '0' + ''' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = ''' + '0' + ''' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	)
    	SELECT pvt.Produkt,' + @cols + ',[Anzahl gemeinsame Orte], [Gesamtzahl], [Ist A1], [Ist A2], [Ist A3]
    	FROM
    	(
    		SELECT Produkt, [Ort]
    		FROM
    		( 
    			SELECT Produkt, [Ort] FROM subUnion
    		) as x
    	) as p
    
    	PIVOT
    	(
    		COUNT(Ort)
    		FOR Ort in (' + @cols + ')
    	) as pvt 
    	--Spalte [Gesamtzahl]
    	LEFT JOIN 
    	( 
    		SELECT COUNT (ISNULL ([Ort], 0)) AS [Gesamtzahl], Produkt 
    		FROM 
    		( 
    			SELECT Produkt, [Ort] FROM subUnion
    		) AS x 
    		GROUP BY Produkt 
    	
    	) AS g ON g.Produkt = pvt.Produkt 
    
    	--Spalte [Anzahl gem. Orte]
    	LEFT JOIN 
    	( 
    		SELECT MAX (ISNULL (ROW, 0)) AS [Anzahl gemeinsame Orte],  Produkt 
    		FROM 
    		( 
    			SELECT [Ort], Produkt, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY [Ort] ASC) AS [ROW] 
    			FROM 
    			( 
    				SELECT Produkt, [Ort] FROM subUnion
    			) AS x 
    			GROUP BY Produkt, [Ort] 
    		) AS v 
    		GROUP BY Produkt 
    	
    	) AS e on e.Produkt = g.Produkt
    
    	--Spalte [Ist Produkt A1]
    	LEFT JOIN 
    	( 
    		SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A1], Produkt 
    		FROM 
    		( 
    			SELECT Produkt, [Ort], Produkte FROM subUnion
    		) AS x 
    		WHERE Produkte = 1
    		GROUP BY Produkt 
    	
    	) AS a ON a.Produkt = pvt.Produkt
    
    	--Spalte [Ist Produkt A2]
    	LEFT JOIN 
    	( 
    		SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A2], Produkt 
    		FROM 
    		( 
    			SELECT Produkt, [Ort], Produkte FROM subUnion
    		) AS x 
    		WHERE Produkte = 2
    		GROUP BY Produkt 
    	
    	) AS b ON b.Produkt = pvt.Produkt
    
    	--Spalte [Ist Produkt A3]
    	LEFT JOIN 
    	( 
    		SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A3], Produkt 
    		FROM 
    		( 
    			SELECT Produkt, [Ort], Produkte FROM subUnion
    		) AS x 
    		WHERE Produkte = 3
    		GROUP BY Produkt 
    	
    	) AS c ON c.Produkt = pvt.Produkt
    
    	ORDER BY [Anzahl gemeinsame Orte] DESC
    	FOR JSON AUTO'
    	--SELECT @result = 
    	EXECUTE(@queryHitliste)
    )
    END;
    GO
    -----------------------------------------------------------
    DECLARE @HitlisteTable AS NVARCHAR(MAX)
    
    ----- Prozedur ausführen
    EXECUTE [SQL_TestHitliste].[dbo].GetHitlisteByKriteriumCol 'Ort', @HitlisteTable OUTPUT;
    ----- Create temporary Table and fill result
    SELECT * INTO Hitliste FROM OpenJson(@HitlisteTable)
    SELECT * FROM Hitliste

    Hier die TestTable:

    CREATE TABLE [dbo].[HitTable$](
    	[Ort] [nvarchar](255) NULL,
    	[ProduktA1] [float] NULL,
    	[IsVorhanden1] [float] NULL,
    	[IsWerbung1] [float] NULL,
    	[ProduktA2] [float] NULL,
    	[IsVorhanden2] [float] NULL,
    	[IsWerbung2] [float] NULL,
    	[ProduktA3] [float] NULL,
    	[IsVorhanden3] [float] NULL,
    	[IsWerbung3] [float] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'A', 1111, 1, 0, 1112, 0, 0, NULL, NULL, NULL)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'B', 2222, 1, 0, 1111, 1, 0, 1113, 0, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'A', 1211, 0, 0, 2222, 1, 1, 1111, 0, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'B', 3333, 1, 0, 1111, 1, 0, 2222, 1, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'C', 1211, 0, 0, 1111, 1, 1, NULL, NULL, NULL)

    Vielleicht kannst du ja evtl. den Code anpassen???

    Gruß

    Jürgen

    Mittwoch, 19. September 2018 08:03
  • Hallo zusammen,

    ich habe eine Lösung gefunden. Ich benötige keine OUTPUT-Parameter. Ich übergebe einfach den Col-Parameter und führe die gespeicherte Procedure aus:

    DECLARE @colParam AS NVARCHAR(max)
    SET @colParam = 'Ort'
    ----- Prozedur ausführen
    EXECUTE [SQL_TestHitliste].[dbo].GetHitlisteByKriteriumCol @colParam;

    Danke für die Eure Überlegungen.

    Gruß

    Jürgen

    Mittwoch, 19. September 2018 08:44