with - t sql stored procedure return table




Funktion vs. gespeicherte Prozedur in SQL Server (11)

Beginnen Sie mit Funktionen, die einen einzelnen Wert zurückgeben. Das Schöne daran ist, dass Sie häufig verwendeten Code in eine Funktion einfügen und sie als Spalte in einer Ergebnismenge zurückgeben können.

Dann könnten Sie eine Funktion für eine parametrisierte Liste von Städten verwenden. dbo.GetCitiesIn ("NY") Gibt eine Tabelle zurück, die als Join verwendet werden kann.

Es ist eine Art, Code zu organisieren. Zu wissen, wann etwas wiederverwendbar ist und wann es Zeitverschwendung ist, wird nur durch Versuch und Irrtum und Erfahrung gewonnen.

Außerdem sind Funktionen in SQL Server eine gute Idee. Sie sind schneller und können sehr mächtig sein. Inline- und Direktauswahl. Vorsichtig nicht überbeanspruchen.

Ich lerne schon seit einiger Zeit Funktionen und Stored Procedure, aber ich weiß nicht warum und wann ich eine Funktion oder eine gespeicherte Prozedur verwenden sollte. Sie sehen gleich aus, vielleicht weil ich ein Neuling bin.

Kann mir jemand sagen warum?


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

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

Funktionen sind berechnete Werte und können keine permanenten Umgebungsänderungen an SQL Server durchführen (dh es sind keine INSERT- oder UPDATE-Anweisungen zulässig).

Eine Funktion kann in SQL-Anweisungen inline verwendet werden, wenn sie einen skalaren Wert zurückgibt, oder sie kann verknüpft werden, wenn sie eine Ergebnismenge zurückgibt.

Ein erwähnenswerter Punkt, der die Antwort zusammenfasst. Danke an @Sean K. Anderson:

Funktionen folgen der Computer-Effizienz-Definition, indem sie einen Wert zurückgeben und die Daten, die sie als Parameter (die Argumente) erhalten, nicht ändern können. Funktionen dürfen nichts ändern, müssen mindestens einen Parameter haben und müssen einen Wert zurückgeben. Gespeicherte Prozesse müssen keinen Parameter haben, können Datenbankobjekte ändern und müssen keinen Wert zurückgeben.


Funktionen und gespeicherte Prozeduren dienen verschiedenen Zwecken. Obwohl es nicht die beste Analogie ist, können Funktionen wörtlich als jede andere Funktion angesehen werden, die Sie in jeder Programmiersprache verwenden würden, aber gespeicherte Prozeduren sind mehr wie einzelne 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 verwendet werden (ein integrierter SQL Server wie DATEDIFF, LEN usw.) oder als Prädikat für eine SQL-Abfrage - z. B. SELECT a, b, dbo.MyFunction(c) FROM table oder SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

Gespeicherte Prozeduren werden verwendet, um SQL-Abfragen in einer Transaktion zu verbinden und mit der Außenwelt zu kommunizieren. Frameworks wie ADO.NET usw. können eine Funktion nicht direkt aufrufen, aber sie können einen gespeicherten Prozess direkt aufrufen.

Funktionen haben jedoch eine versteckte Gefahr: Sie können missbraucht werden und verursachen ziemlich unangenehme Leistungsprobleme: Betrachten Sie diese Abfrage:

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

Wobei MyFunction wie folgt deklariert ist:

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

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Was passiert hier ist, dass die Funktion MyFunction für jede Zeile in der Tabelle MyTable aufgerufen wird. Wenn MyTable 1000 Zeilen hat, sind das weitere 1000 Ad-hoc-Abfragen für die Datenbank. Wenn die Funktion aufgerufen wird, wenn sie in der Spaltenspezifikation angegeben ist, wird die Funktion für jede vom SELECT zurückgegebene Zeile aufgerufen.

Sie müssen also vorsichtig sein, Funktionen zu schreiben. Wenn Sie SELECT aus einer Tabelle in einer Funktion ausführen, müssen Sie sich fragen, ob es besser mit einem JOIN im übergeordneten Stored Proc oder einem anderen SQL-Konstrukt (wie CASE ... WHEN ... ELSE ... ausgeführt werden kann). ENDE).


Hier ist ein praktischer Grund, Funktionen vor gespeicherten Prozeduren zu bevorzugen. Wenn Sie eine gespeicherte Prozedur haben, die die Ergebnisse einer anderen gespeicherten Prozedur benötigt, müssen Sie eine INSERT-EXEC-Anweisung verwenden. Dies bedeutet, dass Sie eine temporäre Tabelle erstellen und eine exec Anweisung verwenden müssen, um die Ergebnisse der gespeicherten Prozedur in die temporäre Tabelle einzufügen. Es ist unordentlich. Ein Problem dabei ist, dass insert-execs nicht geschachtelt werden können .

Wenn Sie bei gespeicherten Prozeduren, die andere gespeicherte Prozeduren aufrufen, stecken bleiben, können Sie darauf stoßen. Wenn die verschachtelte gespeicherte Prozedur einfach ein Dataset zurückgibt, kann sie durch eine Tabellenwertfunktion ersetzt werden, und Sie erhalten diesen Fehler nicht mehr.

( Dies ist ein weiterer Grund, warum wir Geschäftslogik aus der Datenbank heraushalten sollten )


In SQL Server sind Funktionen und gespeicherte Prozedur zwei verschiedene Typen von Entitäten.

Funktion: In der SQL Server-Datenbank werden die Funktionen zum Ausführen einiger Aktionen verwendet, und die Aktion gibt sofort ein Ergebnis zurück. Funktionen sind zwei Arten:

  1. System definiert

  2. Benutzerdefinierte

Gespeicherte Prozeduren: In SQL Server werden die gespeicherten Prozeduren auf dem Server gespeichert. Sie können null, einzelne und mehrere Werte zurückgeben. Gespeicherte Prozeduren sind zwei Arten:

  1. System gespeicherte Prozeduren
  2. Benutzerdefinierte Prozeduren

Schreiben Sie eine benutzerdefinierte Funktion, wenn Sie einen Wert für die Verwendung in anderen SQL-Anweisungen berechnen und zurückgeben möchten. Schreiben Sie eine gespeicherte Prozedur, wenn Sie stattdessen einen möglicherweise komplexen Satz von SQL-Anweisungen gruppieren möchten. Das sind doch zwei recht unterschiedliche Anwendungsfälle!


Um zu entscheiden, wann zu verwenden ist, was die folgenden Punkte helfen könnten-

  1. Gespeicherte Prozeduren können keine Tabellenvariable zurückgeben, wo dies als Funktion möglich ist.

  2. Sie können Stored Procedures verwenden, um die Parameter der Serverumgebung zu ändern, wenn Sie Funktionen nicht verwenden können.

Prost


Gespeicherte Prozeduren werden als Skripte verwendet . Sie führen eine Reihe von Befehlen für Sie aus und Sie können festlegen, dass sie zu bestimmten Zeiten ausgeführt werden.

Funktionen werden als Methoden verwendet. Sie übergeben es etwas und es gibt ein Ergebnis zurück. Sollte klein und schnell sein - macht es im laufenden Betrieb.


Grundunterschied

Die Funktion muss einen Wert zurückgeben, aber in Stored Procedure ist sie optional (Prozedur kann null oder n Werte zurückgeben).

Funktionen können nur Eingabeparameter dafür haben, während Prozeduren Ein- / Ausgabeparameter haben können.

Funktion nimmt einen Eingangsparameter ist obligatorisch, aber gespeicherte Prozedur kann o bis n Eingangsparameter nehmen.

Funktionen können aus Procedure aufgerufen werden, während Prozeduren nicht aus Function aufgerufen werden können.

Fortschrittsdifferenz

Die Prozedur erlaubt sowohl SELECT- als auch DML-Anweisungen (INSERT / UPDATE / DELETE), während Function nur SELECT-Anweisungen erlaubt.

Prozeduren können nicht in einer SELECT-Anweisung verwendet werden, während Function in eine SELECT-Anweisung eingebettet werden kann.

Gespeicherte Prozeduren können nicht in den SQL-Anweisungen irgendwo in der WHERE / HAVING / SELECT-Sektion verwendet werden, während Funktion sein kann.

Funktionen, die Tabellen zurückgeben, können als ein anderes Rowset behandelt werden. Dies kann in JOINs mit anderen Tabellen verwendet werden.

Die Inline-Funktion kann als Ansicht verwendet werden, die Parameter verwendet und in JOINs und anderen Rowset-Operationen verwendet werden kann.

Ausnahme kann durch try-catch-Block in einer Prozedur behandelt werden, während try-catch-Block nicht in einer Funktion verwendet werden kann.

Wir können für Transaktionsverwaltung in Prozedur gehen, während wir in Funktion nicht gehen können.

source


  • Funktionen können in einer Select-Anweisung verwendet werden, wo dies bei Prozeduren nicht möglich ist.

  • Stored Procedure verwendet sowohl Eingabe- als auch Ausgabeparameter, aber Functions nimmt nur Eingabeparameter.

  • Funktionen können keine Werte vom Typ text, ntext, image & timestamps liefern, wo dies möglich ist.

  • Funktionen können als benutzerdefinierte Datentypen in create table verwendet werden, Prozeduren jedoch nicht.

*** ZB: -create table <tablename>(name varchar(10),salary getsal(name))

Hier ist getsal eine benutzerdefinierte Funktion, die einen Gehaltstyp zurückgibt, wenn die Tabelle erstellt wird, wird kein Speicher für den Gehaltstyp zugewiesen, und die getsale Funktion wird ebenfalls nicht ausgeführt. Wenn wir jedoch einige Werte aus dieser Tabelle abrufen, wird die getsale Funktion ausgeführt return Type wird als Ergebnismenge zurückgegeben.





sql-function