wake-up-neo.com

TSQL - If..Else-Anweisung in Tabellenwertfunktionen - kann nicht durchlaufen werden

Vor dem Posten habe ich einige Artikel über die Entwicklung von USD-Funktionen gelesen, aber keine Lösung für mein Problem gefunden ...

Ich habe eine sehr einfache Datenbank, die Basketballspieler speichert und aus ID, Alter, Größe und Name besteht. Ich möchte eine Funktion 'height' mit einem Parameter @set varchar (10) implementieren, der je nach einem @set-Wert unterschiedliche select-Anweisungen auslöst

was ich zu implementieren versuchte, war im Pseudo-Code:

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS TABLE
AS
BEGIN

    IF  (@set = 'tall')
         SELECT * from player where height > 180

    ELSE IF (@set = 'average')
         SELECT * from player where height >= 155 and height <=175

    ELSE IF (@set = 'low')
         SELECT * from player where height < 155
END

Könnte mir jemand einen Hinweis geben, wie ich es umsetzen kann?

sqlsql-serverdatabasetsqluser-defined-functions
16
Artur

Die einfachste Form ist immer die Beste

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS TABLE
AS RETURN
SELECT * from player
where ((@set = 'tall' and height > 180)
   or (@set = 'average' AND height >= 155 and height <=175)
   or (@set = 'low' AND height < 155))
GO

Dieses Formular wird INLINE-Tabellenfunktion genannt. Das heißt, SQL Server kann es beliebig erweitern, um den Player direkt mit anderen Tabellen in einer größeren Abfrage zu verbinden, wodurch er unendlich ausführt1 besser als eine Funktion mit mehreren Anweisungen.

Sie können dies jedoch vorziehen, so dass Ihre Bereiche vollständig sind (Sie haben eine Lücke zwischen 175 und 180).

where ((@set = 'tall' and height > 180)
   or (@set = 'average' AND height >= 155 and height <= 180)
   or (@set = 'low' AND height < 155))

SQL Server sorgt für einen Kurzschluss der Zweige, wenn die Variable @set analysiert wird.

1 übertrieben, aber nur geringfügig

20
RichardTheKiwi

Du warst nah. Die Verwendung einer Tabellenwertfunktion mit mehreren Anweisungen erfordert, dass die Rückgabetabelle in der Funktion angegeben und gefüllt wird:

CREATE FUNCTION [dbo].[age](@set varchar(10))
RETURNS @Players TABLE
(
    -- Put the players table definition here
) 
AS
BEGIN

    IF  (@set = 'tall')
         INSERT INTO @Players SELECT * from player where height > 180

    ELSE IF (@set = 'average')
         INSERT INTO @Players SELECT * from player where height >= 155 and height <=175

    ELSE IF (@set = 'low')
         INSERT INTO @Players SELECT * from player where height < 155

    RETURN -- @Players (variable only required for Scalar functions)

END

Ich würde empfehlen, einen Inline-TVF zu verwenden, wie Richards Antwort zeigt. Es kann die Tabellenrückgabe aus Ihrer Anweisung ableiten.

Beachten Sie auch, dass Multi-Statement- und Inline-TVFs sehr unterschiedlich sind. Ein Inline-TVF ist für das Optimierungsprogramm weniger eine Blackbox und eher eine parametrisierte Ansicht, da das Optimierungsprogramm Dinge mit anderen Tabellen und Ansichten in demselben Ausführungsplan neu anordnen kann.

16
Cade Roux

Warum müssen Sie dies hart codieren, erstellen Sie eine Höhentabelle und greifen Sie dann alle für den Bereich gültigen Höhen auf

SELECT * from player p
join Heights h on p.height between h.heightStart and h.heightEnd 
WHERE h.height  = @set
10
SQLMenace

Das sollte funktionieren.

SELECT * FROM player 
WHERE
  height > CASE 
            WHEN @set = 'tall' THEN 180
            WHEN @set = 'average' THEN 154
            WHEN @set = 'low' THEN 0
          END

Ich lasse den Fall zu Ihrem Vergnügen.

3
Hogan

Etwas wie das:

CREATE FUNCTION [dbo].[Age](@set VARCHAR(10))  
RETURNS @Players TABLE
(
    playerId INT,
    Name VARCHAR(50)
) 
AS  
BEGIN 

    INSERT INTO @Players
    SELECT playerId, Name
    FROM player 
    WHERE CASE WHEN @set = 'tall' AND height > 180 THEN 1
    WHEN @set = 'average' AND height BETWEEN 155 AND 180 THEN 1
    WHEN @set = 'low' AND height < 155 THEN 1 ELSE 0 END = 1

    RETURN
END
2
Lamak

Die Tabellenwertfunktion kann auf folgende Weise mit IF-Bedingungen verwendet werden.

CREATE function [dbo].[AA] 
(
@abc varchar(10)
)
Returns  @mytable table
(
supname nvarchar(10), [add] nvarchar(10)
)
AS
begin
-- lOAD WHATEVER THINGS YOU REQUIRED INTO THIS DYNAMIC TABLE
if (@abc ='hh')
    insert into @mytable (supname, [add]) values ('hh','gg'+ @abc)
else
    insert into @mytable (supname, [add]) values ('else','gg'+ @abc)
return
end

--select * from [dbo].[AA]('SDAASF')
1
user2316060

Laut Itzik Ben-Gan in seinem Buch "TSQL Querying" (Itzik Ben-Gan ua , (c) 2015 Microsoft Press, ISBN 978-0-7356-8504-8, S. 215) ".. . Ich finde, dass Inline-TVFs ein großartiges Werkzeug sind, das die Verkapselung der Logik und Wiederverwendbarkeit ohne Performanceprobleme von UDFs ermöglicht ... " 

Er sagt auch, wenn Sie " ... einen wiederverwendbaren Tabellenausdruck wie eine View benötigen, müssen Sie jedoch auch Eingabeparameter an den Tabellenausdruck übergeben ... TSQL stellt Inline-Tabellenwertfunktionen (TVFs) bereit. " 

Dieser Typ von 'IF' ( Inline-Funktion - ein bestimmter Typ in sys.objects ) verwendet den Ausgabespezifizierer 'RETURNS TABLE' und kann anscheinend nicht BEGIN/END enthalten. Die Syntax und die Berechtigungen sind sehr restriktiv, dennoch sehen wir gute Optimierung und Leistung. Diese Faktoren werden durch die von @ryk ermittelten Zeiten angezeigt.

0
CubeSpark