none
Variable innerhalb einer Table Valued Function RRS feed

  • Frage

  • Hallo Zusammen,

    ich nutze eine Table Valued Function mit mehreren Eingabeparametern. Nun möchte ich zusätzlich zu den Parametern auch noch eine Variable definieren um aus zwei Eingabeparametern (Monat und Jahr) einen Wert (Datum des ersten Tages dieses Monats) zu errechnen.

    Meine Table Valued Function sieht vereinfacht so aus:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    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    dbo.meineTabelle R
               WHERE       (Linkbeginn <= @Startdatum AND (Linkende >= @Enddatum) OR
                                                          Linkende IS NULL) AND SubjektNr = @SubjektNr or @SubjektNr is null));

    Doch wo (und wie) kann ich die beiden Variablen @Startdatum und @Enddatum definieren und dann errechnen

    Errechnen des Datums dann so?:

    SET @Startdatum = CAST('01.' + CAST(RgMonat AS varchar(2)) + '.' + CAST(RgJahr AS varchar(4)) AS Datetime)

    SET @Enddatum = DATEADD(m,1,@Startdatum)

    Vielen Dank für Hilfe

    Viele Grüße

    Patrick

    Freitag, 13. Februar 2015 07:54

Antworten

  • 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]

    Freitag, 13. Februar 2015 08:40
  • 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



    Freitag, 13. Februar 2015 08:43
    Moderator

Alle Antworten

  • 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]

    Freitag, 13. Februar 2015 08:40
  • 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



    Freitag, 13. Februar 2015 08:43
    Moderator