none
Dynamic SQL: Mehrwertiger Parameter und WHERE IN => Fehler bei der Berichtsausführung RRS feed

  • Frage

  • set @sql=

    'SELECT SUM(WERT) AS ID

     FROM    DETAILV

     WHERE    ' + cast(@Reportfiltergruppe as varchar) + ' in ( ''' + REPLACE(@Reportfilter, ',',''',''') + ''' )‘

    Ich habe ein dynamisches SQL-Statement, mit dem mehrwertigen Parameter @Reportfilter. Die obige WHERE IN - Bedingung bereitet allerdings Probleme. Der Fehler tritt nach dem IN auf. Wenn ich einen einzelnen Wert selektiere wird die Abfrage erfolgreich ausgeführt. Wenn ich mehr als einen Wert selektiere erhalte ich folgende Fehlermeldung:

    (Die replace-Funktion erfordert 3 Argument(e))

    Im Abfrage-Designer wird die Abfrage, auch bei mehrwertigen Eingaben, immer korrekt ausgeführt und ich erhalte das gewünschte Resultset, nur halt nicht in der Reportvorschau.

    Donnerstag, 15. März 2012 07:41

Alle Antworten

  • Hi,

    welche Werte stehen denn in der Variablen @Reportfilter? Hast du einmal mit dem SQL Profiler geprüft, welche Abfrage genau beim SQL Server ankommt?

    Grüße

    Oliver

    Donnerstag, 15. März 2012 07:51
  • Sofern Dein Report auf einer Prozdur aufbaut, würde ich Dir vorschlagen, einen anderen Weg zu gehen.

    Warum so umständlich "parsen", wie Du es machst.
    Lege eine Tabellevariable an und lasse die Werte aus @ReportFilter in diese Tabelle laufen.
    Die eigentliche Abfrage wird dann mittels JOIN zu der Tabellenvariablen abgefragt.
    Damit erschlägst Du gleich mehrere Probleme:

    - Kein kompliziertes Parsen
    - Keine SQL-Injection
    - Speicherung von Abfrageplan

    Hier mal ein Beispiel (on the fly)

    DECLARE	@ReportFilterList	nvarchar(255)	= '1, 5, 10, 20, 12'
    DECLARE	@Separator			nvarchar(1)		=	','
    DECLARE	@tmp_String			nvarchar(255)
    
    DECLARE	@t TABLE (LookupValues	int)
    
    -- Zunächst alle Leerzeichen aus dem ReportFilter
    -- nur für einfacheres Parsen
    SET	@ReportFilterList = REPLACE(@ReportFilterList, ' ', '')
    
    WHILE CHARINDEX(@Separator, @ReportFilterList) != 0
    BEGIN
    	SET	@tmp_String = LEFT(@ReportFilterList, CHARINDEX(@Separator, @ReportFilterList) - 1)
    	SET	@ReportFilterList = SUBSTRING(@ReportFilterList, CHARINDEX(@Separator, @ReportFilterList) +1, LEN(@ReportFilterList))
    	
    	INSERT INTO @t (LookupValues) VALUES (@tmp_String)
    END
    
    SET	@tmp_String	=	@ReportFilterList
    INSERT INTO @t (LookupValues) VALUES (@tmp_String)
    
    -- Nun Dein Select - aber nicht mehr dynamisch
    SELECT SUM(v.Wert) AS Id
    FROM  dbo.detailv v INNER JOIN @t t
          ON (v.[ReportFilterGruppe] = t.LookupValues)

    Nur mal so als Denkansatz :)

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Donnerstag, 15. März 2012 07:58
  • In Deinem Post im en-US Forum hast Du Dir bei der Beschreibung etwas mehr Mühe gegeben.

    Und bevor hier alle das gleiche wie dort durch exerzieren, hier der Link: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9a9b9d7b-1abc-421e-8540-452bf459ad29/#c54dd359-b0ff-4c35-8d98-a8a1f7ff4e55


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Donnerstag, 15. März 2012 08:06
  • Hi,

    welche Werte stehen denn in der Variablen @Reportfilter? Hast du einmal mit dem SQL Profiler geprüft, welche Abfrage genau beim SQL Server ankommt?

    Grüße

    Oliver

    SQL Profiler-Ergebnis aus Abfrage-Designer mit @Reportfiltergruppe = kreisbez / @Reportfilter = Discount,LEH

    set @sql=
    ''SELECT SUM(WERT) AS ID
      FROM    DETAILV
      WHERE    '' + cast(@Reportfiltergruppe as varchar) + '' in ('''''' + REPLACE('''' + @Reportfilter + '''', '','','''''','''''') + '''''') ''

    exec (@sql)',N'@Reportfiltergruppe nvarchar(8),@Reportfilter nvarchar(12)',@Reportfiltergruppe=N'kreisbez',@Reportfilter=N'Discount,LEH'


    SQL Profiler-Ergebnis aus Reportvorschau mit den gleichen Parametern

    set @sql=
    ''SELECT SUM(WERT) AS ID
      FROM    DETAILV
      WHERE    '' + cast(@Reportfiltergruppe as varchar) + '' in ('''''' + REPLACE('''' + N''Discount'',N''Einkaufskunden'' + '''', '','','''''','''''') + '''''') ''

    exec (@sql)',N'@Reportfiltergruppe nvarchar(8)',@Reportfiltergruppe=N'kreisbez'

    Donnerstag, 15. März 2012 08:07
  • Sofern Dein Report auf einer Prozdur aufbaut, würde ich Dir vorschlagen, einen anderen Weg zu gehen.

    Warum so umständlich "parsen", wie Du es machst.
    Lege eine Tabellevariable an und lasse die Werte aus @ReportFilter in diese Tabelle laufen.
    Die eigentliche Abfrage wird dann mittels JOIN zu der Tabellenvariablen abgefragt.
    Damit erschlägst Du gleich mehrere Probleme:

    - Kein kompliziertes Parsen
    - Keine SQL-Injection
    - Speicherung von Abfrageplan

    Hier mal ein Beispiel (on the fly)

    DECLARE	@ReportFilterList	nvarchar(255)	= '1, 5, 10, 20, 12'
    DECLARE	@Separator			nvarchar(1)		=	','
    DECLARE	@tmp_String			nvarchar(255)
    
    DECLARE	@t TABLE (LookupValues	int)
    
    -- Zunächst alle Leerzeichen aus dem ReportFilter
    -- nur für einfacheres Parsen
    SET	@ReportFilterList = REPLACE(@ReportFilterList, ' ', '')
    
    WHILE CHARINDEX(@Separator, @ReportFilterList) != 0
    BEGIN
    	SET	@tmp_String = LEFT(@ReportFilterList, CHARINDEX(@Separator, @ReportFilterList) - 1)
    	SET	@ReportFilterList = SUBSTRING(@ReportFilterList, CHARINDEX(@Separator, @ReportFilterList) +1, LEN(@ReportFilterList))
    	
    	INSERT INTO @t (LookupValues) VALUES (@tmp_String)
    END
    
    SET	@tmp_String	=	@ReportFilterList
    INSERT INTO @t (LookupValues) VALUES (@tmp_String)
    
    -- Nun Dein Select - aber nicht mehr dynamisch
    SELECT SUM(v.Wert) AS Id
    FROM  dbo.detailv v INNER JOIN @t t
          ON (v.[ReportFilterGruppe] = t.LookupValues)

    Nur mal so als Denkansatz :)

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Den Ansatz finde ich gut, allerdings sind stored procedures keine Option, da der Report rein über das rdl laufen muss.

    Wäre es denn möglich über den SQL-Block einen Temp Table zur Laufzeit zu erstellen den ich danach abfrage?

    Donnerstag, 15. März 2012 08:23
  • Den Ansatz finde ich gut, allerdings sind stored procedures keine Option, da der Report rein über das rdl laufen muss.

    Sorry - aber den Satz muß ich jetzt nicht verstehen, oder?
    Du kannst doch als Datengrundlage für das Dataset eine SP verwenden - wo ist denn da das Problem?

    FYI: RDL = Report Definition Language
    als rdl wird die Reportdatei als solche bezeichnet - sie hat NICHTS mit dem Source selbst zu tun!


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Donnerstag, 15. März 2012 08:27
  • Den Ansatz finde ich gut, allerdings sind stored procedures keine Option, da der Report rein über das rdl laufen muss.

    Sorry - aber den Satz muß ich jetzt nicht verstehen, oder?
    Du kannst doch als Datengrundlage für das Dataset eine SP verwenden - wo ist denn da das Problem?

    FYI: RDL = Report Definition Language
    als rdl wird die Reportdatei als solche bezeichnet - sie hat NICHTS mit dem Source selbst zu tun!


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Nein, so war es auch nicht gemeint. ;-) Vielleicht habe ich mich missverständlich ausgedrückt. Das Ziel ist den Report komplett in Reporting Services über eine dynamische SQL-Abfrage zu erstellen (möglichst ohne stored procedures). Wenn es ohne stored procedure aber definitiv nicht funktioniert, muss ich natürlich diesen Weg gehen...
    Donnerstag, 15. März 2012 09:10
  • Hallo SQL_Pet,

    ---  Das Ziel ist den Report komplett in Reporting Services über eine dynamische SQL-Abfrage zu erstellen  ---

    hmm - ich kann Dir imer noch nicht folgen :)
    Spaß beiseite; Du erstellst den Bericht doch im BI-Studio und distributierst in anschließend auf den ReportServer.
    Demzufolge muß doch während der Erstellung das Dataset für en Report bestimmt werden.
    Und genau dieses Dataset kann ein SQL-Statement, eine View oder eine SP sein.

    Die Parameter gibst Du dann über die Webseite ein und übergibst sie der SP.
    Sehe ich die geringsten Probleme - ansonsten sehe ich vor allen Dingen SQLInjection bei Deiner Lösung als Gefahr!


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Donnerstag, 15. März 2012 09:45
  • Hallo!
    Das Problem besteht ja darin, dass im Report der Parameter nur als Platzhalter fungiert und im SQL 1:1 durch den zusammengesetzten Text ersetzt wird.

    Die sich daraus ergebende Syntax ist nicht für Konkatenation geeignet. Der Trick mit der Klammerung dieses Parameters in einfache Hochkommata funktioniert nicht, da dieses Konstrukt als gültiger String erkannt wird und nicht mehr als Platzhalter mit Hochkommata drum rum.

    Was ich mir vorstellen könnte, wäre eine Tabelle, die man vorab entsprechend füllt und danach als Join-Bedingung verwendet:

    Insert into tabSelektionskriterien (Wert)
    Select distinct Wert
    from Quelle where Bedingung in (@ReportParameter);
    
    ...
    from tab a
    Inner Join tabSelektionskriterien S on a.Wert = S.Wert;

    Hier kann man auch wieder keine temporäre Tabelle oder Tabellenvariable verwenden, da diese im dynamischen SQL nicht bekannt sind.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Freitag, 16. März 2012 09:38