Funktion vs. Gespeicherte Prozedur in SQL Server

Question

Ich lerne schon seit einiger Zeit Funktionen und Stored Procedure, aber ich weiß nicht, warum und wann ich eine Funktion oder eine Stored Procedure verwenden sollte. Für mich sehen sie gleich aus, vielleicht weil ich in diesem Bereich ein Neuling bin.

Kann mir jemand sagen, warum?

Answer 1

Funktionen sind berechnete Werte und können keine permanenten Änderungen der Umgebung vornehmen. SQL Server (d.h. keine INSERT o UPDATE Anweisungen erlaubt).

Eine Funktion kann inline verwendet werden in SQL Anweisungen, wenn sie einen Einzelwert zurückgibt, oder kann mit ihr verbunden werden, wenn sie eine Ergebnismenge zurückgibt.

Ein erwähnenswerter Punkt aus den Kommentaren, die die Antwort zusammenfassen. Dank an @Sean K Anderson:

Funktionen folgen der Informatik-Definition insofern, als sie einen Wert zurückgeben MÜSSEN und die Daten, die sie als Parameter erhalten, nicht verändern können (die Argumente) erhalten. Funktionen dürfen nichts verändern, müssen mindestens einen Parameter haben und müssen einen Wert zurückgeben. Gespeicherte procs müssen keinen Parameter haben, können Datenbankobjekte ändern, und müssen keinen Wert zurückgeben.

Answer 2

Der Unterschied zwischen SP und UDF ist unten aufgeführt:

Gespeicherte Prozedur (SP)

Funktion (UDF - Benutzerdefiniert)

SP kann Null, einzelne oder mehrere Werte zurückgeben.

Die Funktion muss einen einzigen Wert zurückgeben (der ein Skalar oder eine Tabelle sein kann).

Wir können die Transaktion in SP verwenden.

Wir können die Transaktion nicht in UDF verwenden.

SP kann Eingabe-/Ausgabeparameter haben.

Einziger Eingabeparameter.

Wir können die Funktion von SP aus aufrufen.

Wir können SP nicht aus einer Funktion heraus aufrufen.

Wir können SP nicht in SELECT/ WHERE/ HAVING-Anweisungen verwenden.

Wir können UDF in SELECT/ WHERE/ HAVING-Anweisungen verwenden.

Wir können die Ausnahmebehandlung mit dem Try-Catch-Block in SP verwenden.

Wir können keinen Try-Catch-Block in UDF verwenden.

Answer 3

Funktionen und gespeicherte Prozeduren dienen unterschiedlichen Zwecken. Obwohl es nicht die beste Analogie ist, können Funktionen buchstäblich als jede andere Funktion betrachtet werden, die Sie in jeder Programmiersprache verwenden würden, aber gespeicherte Prozeduren sind eher wie individuelle Programme oder ein Batch-Skript.

Funktionen haben normalerweise einen Ausgang und optional Eingänge. Die Ausgabe kann dann als Eingabe für eine andere Funktion (eine in SQL Server integrierte Funktion wie DATEDIFF, LEN usw.) oder als Prädikat für eine SQL-Abfrage verwendet werden - z. B., SELECT a, b, dbo.MyFunction(c) FROM table o SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

Stored Procs werden verwendet, um SQL-Abfragen in einer Transaktion zusammenzubinden und eine Schnittstelle zur Außenwelt zu schaffen. Frameworks wie ADO.NET usw. können eine Funktion nicht direkt aufrufen, wohl aber einen Stored Proc.

Funktionen bergen jedoch eine versteckte Gefahr: Sie können missbraucht werden und zu unangenehmen Leistungsproblemen führen, wie diese Abfrage zeigt:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Wo MyFunction deklariert ist als:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

In diesem Fall wird die Funktion MyFunction für jede Zeile in der Tabelle MyTable aufgerufen. Wenn MyTable 1000 Zeilen hat, dann sind das 1000 weitere Ad-hoc-Abfragen an die Datenbank. Wenn die Funktion aufgerufen wird, wenn sie in der Spaltenspezifikation angegeben ist, wird die Funktion für jede Zeile aufgerufen, die von SELECT zurückgegeben wird.

Sie müssen also beim Schreiben von Funktionen vorsichtig sein. Wenn Sie SELECT aus einer Tabelle in einer Funktion ausführen, müssen Sie sich fragen, ob dies nicht besser mit einem JOIN im übergeordneten Stored Proc oder einem anderen SQL-Konstrukt (wie CASE ... WHEN ... ELSE ... END) durchgeführt werden kann.

Answer 4

Unterschiede zwischen gespeicherten Prozeduren und benutzerdefinierten Funktionen:

• Gespeicherte Prozeduren können nicht in Select-Anweisungen verwendet werden.
• Gespeicherte Prozeduren unterstützen Deferred Name Resolution.
• Gespeicherte Prozeduren werden im Allgemeinen für die Ausführung von Geschäftslogik verwendet.
• Gespeicherte Prozeduren können jeden Datentyp zurückgeben.
• Gespeicherte Prozeduren können eine größere Anzahl von Eingabeparametern akzeptieren als benutzerdefinierte Funktionen. Gespeicherte Prozeduren können bis zu 21.000 Eingabeparameter haben.
• Stored Procedures können Dynamic SQL ausführen.
• Gespeicherte Prozeduren unterstützen die Fehlerbehandlung.
• Nicht-deterministische Funktionen können in gespeicherten Prozeduren verwendet werden.

---

• Benutzerdefinierte Funktionen können in Select-Anweisungen verwendet werden.
• Benutzerdefinierte Funktionen unterstützen keine Deferred Name Resolution.
• Benutzerdefinierte Funktionen werden im Allgemeinen für Berechnungen verwendet.
• Benutzerdefinierte Funktionen sollten einen Wert zurückgeben.
• Benutzerdefinierte Funktionen können keine Bilder zurückgeben.
• Benutzerdefinierte Funktionen akzeptieren eine geringere Anzahl von Eingabeparametern als gespeicherte Prozeduren. UDFs können bis zu 1.023 Eingabeparameter haben.
• Temporäre Tabellen können nicht in benutzerdefinierten Funktionen verwendet werden.
• Benutzerdefinierte Funktionen können kein Dynamic SQL ausführen.
• Benutzerdefinierte Funktionen unterstützen keine Fehlerbehandlung. RAISEERROR OR @@ERROR sind in UDFs nicht erlaubt.
• Nicht-deterministische Funktionen können nicht in UDFs verwendet werden. Zum Beispiel, GETDATE() können nicht in UDFs verwendet werden.

Answer 5

SPEICHERVERFAHREN

FUNKTION (BENUTZERDEFINIERTE FUNKTION)

Verfahren kann 0, einzelne oder mehrere Werte zurückgeben

Funktion kann nur einen einzigen Wert zurückgeben

Prozedur kann Eingabe- und Ausgabeparameter haben

Funktion kann nur Eingabeparameter haben

Prozedur kann nicht aus einer Funktion heraus aufgerufen werden

Funktionen können von Prozeduren aufgerufen werden

Prozedur erlaubt sowohl Select- als auch DML-Anweisungen

Funktion erlaubt nur Select-Anweisung in ihr

Ausnahmen können durch einen try-catch-Block in einer Prozedur behandelt werden

Try-catch-Block kann nicht in einer Funktion verwendet werden

Wir können uns für die Verwaltung von Transaktionen im Verfahren

Wir können nicht für die Verwaltung von Transaktionen in Funktion gehen

Prozedur kann nicht in einer Select-Anweisung verwendet werden

Funktion kann in eine Select-Anweisung eingebettet werden

Prozedur kann den Zustand der Datenbank beeinflussen, d.h. sie kann CRUD-Operationen in der Datenbank durchführen

Die Funktion kann den Zustand der Datenbank nicht beeinflussen, d.h. sie kann keine CRUD-Operation an der Datenbank durchführen.

Verfahren kann temporäre Tabellen verwenden

Funktion kann keine temporären Tabellen verwenden

Verfahren kann die Parameter der Serverumgebung ändern

Die Funktion kann die Umgebungsparameter nicht verändern

Verfahren verwenden können, wenn wir stattdessen eine möglicherweise komplexe Gruppe von SQL-Anweisungen gruppieren wollen

Die Funktion kann verwendet werden, wenn ein Wert zur Verwendung in anderen SQL-Anweisungen berechnet und zurückgegeben werden soll.