Variable innerhalb einer Table Valued Function

Antworten

• 

  

  1

  Anmelden

  Hallo Partick,

  siehe CREATE FUNCTION (Transact-SQL) => Beispiel "C. Creating a multi-statement table-valued function". Für Deinen Fall sieht es grob so aus:

  CREATE FUNCTION [dbo].[ft_meineTVFunction]
       (@SubjektNr int = NULL,
        @RgMonat as int = null,
        @RgJahr as int = null)
  
  RETURNS @result TABLE (wert1 varchar(100), wert2 varchar(100))
  AS
  BEGIN
      
      DECLARE @Startdatum AS datetime, @Enddatum AS datetime;
      
      SET @Startdatum = CAST('01.' + CAST(@RgMonat AS varchar(2)) + '.' + CAST(@RgJahr AS varchar(4)) AS Datetime) ;
      SET @Enddatum = DATEADD(m,1, @Startdatum);
  
      SELECT R.MeinWert1, R.MeinWert2
      FROM    dbo.meineTabelle R
              WHERE  (Linkbeginn <= @Startdatum AND 
                     (Linkende >= @Enddatum) OR
                      Linkende IS NULL) AND 
                      SubjektNr = @SubjektNr 
                      or @SubjektNr is null;
  END

  
  wobei mir Deine AND/OR Kombination in der WHERE leicht merkwürdig anmutet. Und bei der Datumskonvertierung nutze lieber ISO Format und die CONVERT statt der cast Funktion.

  ---

  Olaf Helper

  [ Blog] [ Xing] [ MVP]

  • Als Antwort vorgeschlagen Dimitar DenkovMicrosoft contingent staff, Moderator Montag, 16. Februar 2015 12:27
  • Als Antwort markiert Dimitar DenkovMicrosoft contingent staff, Moderator Montag, 23. Februar 2015 11:58

  Freitag, 13. Februar 2015 08:40

  Antworten

  |

  Zitieren
• 

  

  1

  Anmelden

  Hallo Patrick,

  sobald Du sowas machst, wird aus deiner "Inline Table-Valued function" eine "Multistatement Table-Valued function" (MTVF) und das wiederum kann dir in gewissen Konstellationen performancetechnisch das Genick brechen. Ein Einbruch um den Faktor 10, 20, ... ist dabei nicht selten, wobei das u.U. auch erst später auftreten kann, wenn die abgefragten Datenmengen ein wenig gewachsen sind.

  Eine ausführliche Beschreibung der beiden Funktionsarten findest Du bspw. hier:

    http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/

  Verkürzt (und sicher nicht 100% korrekt aber wohl 98^^) gesagt: Eine MTVF ist für den SQL Optimierer eine Blackbox, die nicht optimiert werden kann. Bei komplexeren Abfragen kann es daher dazu kommen, dass anstelle von Indexverwendung Full Table Scans durchgeführt werden, usw.

  Ich müsste das auch schmerzhaft lernen, dass es besser ist, zusätzliche Parameter an die Funktion zu übergeben oder mit den vorhandenen zu rechnen als wegen ein, zwei, drei Variablen die Performance extrem einbrechen zu lassen.

  Soll heißen: Entweder übergibst Du das Startdatum als Parameter gleich mit oder Du verwendest das von dir gezeigte:

  CAST('01.' + CAST(RgMonat AS varchar(2)) + '.' + CAST(RgJahr AS varchar(4)) AS Datetime) 

  einfach in der WHERE Klausel des SQL Statements,

  DATEADD(m,1,@Startdatum)

  ebenso. Im gesamten sieht das dann in etwa so aus:

  CREATE FUNCTION [dbo].[ft_meineTVFunction]
  (
      @SubjektNr int = NULL,
      @RgMonat as int = null,
      @RgJahr as int = null
  )
  RETURNS TABLE
  AS
  RETURN
  (
  SELECT  R.MeinWert1,
          R.MeinWert2
  FROM    meineTabelle R
  WHERE   Linkbeginn <= CAST('01.' + CAST(RgMonat AS varchar(2)) + '.' + CAST(RgJahr AS varchar(4)) AS Datetime)
  AND     (
              Linkende   >= DATEADD(m,1,CAST('01.' + CAST(RgMonat AS varchar(2)) + '.' + CAST(RgJahr AS varchar(4)) AS Datetime))
           OR Linkende IS NULL
          )
  AND     ISNULL( SubjektNr, @SubjektNr ) = @SubjektNr
  )
  

  Ich hab dein Statement allerdings etwas umgestellt, da ich wie Olaf der Meinung bin, dass es so wie von dir gepostet eher nicht richtig sein dürfte.
  

  ---

  Gruß, Stefan
  Microsoft MVP - Visual Developer ASP/ASP.NET
  http://www.asp-solutions.de/ - Consulting, Development
  http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community
  

  
  
  

  • Bearbeitet Stefan FalzModerator Freitag, 13. Februar 2015 09:02
  • Als Antwort vorgeschlagen Dimitar DenkovMicrosoft contingent staff, Moderator Montag, 16. Februar 2015 12:27
  • Als Antwort markiert Dimitar DenkovMicrosoft contingent staff, Moderator Montag, 23. Februar 2015 12:01

  Freitag, 13. Februar 2015 08:43

  Antworten

  |

  Zitieren

  Moderator