Fonction et procédure stockée dans SQL Server

Question

J'ai appris les fonctions et les procédures stockées depuis un certain temps, mais je ne sais pas pourquoi et quand je dois utiliser une fonction ou une procédure stockée. Elles me paraissent identiques, peut-être parce que je suis un peu novice en la matière.

Quelqu'un peut-il me dire pourquoi ?

Answer 1

Les fonctions sont des valeurs calculées et ne peuvent pas effectuer des modifications permanentes de l'environnement de l'entreprise. SQL Server (c'est-à-dire qu'il n'y a pas de INSERT ou UPDATE autorisés).

Une fonction peut être utilisée en ligne dans SQL s'il retourne une valeur scalaire ou peut être joint s'il retourne un ensemble de résultats.

Un point à noter dans les commentaires, qui résument la réponse. Merci à @Sean K Anderson :

Les fonctions suivent la définition de l'informatique en ce sens qu'elles DOIVENT renvoyer une valeur et ne peuvent pas modifier les données qu'elles reçoivent en paramètre (les arguments). (les arguments). Les fonctions ne sont pas autorisées à modifier quoi que ce soit, doivent avoir au moins un paramètre, et elles doivent retourner une valeur. Stocké procs ne doivent pas avoir de paramètre, peuvent modifier les objets de la base de données, et ne doivent pas retourner une valeur.

Answer 2

La différence entre SP et UDF est indiquée ci-dessous :

Procédure stockée (SP)

Fonction (UDF - User Defined)

SP peut renvoyer zéro, une ou plusieurs valeurs.

La fonction doit retourner une seule valeur (qui peut être un scalaire ou un tableau).

Nous pouvons utiliser la transaction dans la PS.

Nous ne pouvons pas utiliser la transaction dans UDF.

SP peut avoir un paramètre d'entrée/sortie.

Seul paramètre d'entrée.

Nous pouvons appeler la fonction à partir de SP.

On ne peut pas appeler SP depuis une fonction.

Nous ne pouvons pas utiliser la PS dans l'instruction SELECT/ WHERE/ HAVING.

Nous pouvons utiliser l'UDF dans l'instruction SELECT/ WHERE/ HAVING.

Nous pouvons utiliser la gestion des exceptions en utilisant le bloc Try-Catch dans SP.

Nous ne pouvons pas utiliser le bloc Try-Catch dans l'UDF.

Answer 3

Les fonctions et les procédures stockées ont des objectifs distincts. Bien que ce ne soit pas la meilleure analogie, les fonctions peuvent être considérées littéralement comme n'importe quelle autre fonction que vous utiliseriez dans n'importe quel langage de programmation, mais les procédures stockées ressemblent davantage à des programmes individuels ou à un script de lot.

Les fonctions ont normalement une sortie et éventuellement des entrées. La sortie peut ensuite être utilisée comme entrée d'une autre fonction (une fonction intégrée du serveur SQL telle que DATEDIFF, LEN, etc.) ou comme prédicat d'une requête SQL - par exemple, SELECT a, b, dbo.MyFunction(c) FROM table ou SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

Les procs stockés sont utilisés pour lier des requêtes SQL dans une transaction et s'interfacer avec le monde extérieur. Les frameworks tels que ADO.NET, etc. ne peuvent pas appeler directement une fonction, mais ils peuvent appeler directement une proc stockée.

Les fonctions ont cependant un danger caché : elles peuvent être mal utilisées et causer des problèmes de performance plutôt désagréables : considérez cette requête :

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

Où MaFonction est déclarée comme :

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

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Ce qui se passe ici, c'est que la fonction MaFonction est appelée pour chaque ligne du tableau MaTable. Si MyTable comporte 1000 lignes, cela représente 1000 requêtes ad hoc supplémentaires dans la base de données. De même, si la fonction est appelée lorsqu'elle est spécifiée dans la spécification de colonne, la fonction sera appelée pour chaque ligne renvoyée par le SELECT.

Il faut donc faire attention à l'écriture des fonctions. Si vous effectuez un SELECT à partir d'une table dans une fonction, vous devez vous demander s'il ne serait pas préférable d'utiliser un JOIN dans la proc stockée parente ou une autre construction SQL (telle que CASE ... WHEN ... ELSE ... END).

Answer 4

Différences entre les procédures stockées et les fonctions définies par l'utilisateur :

• Les procédures stockées ne peuvent pas être utilisées dans les instructions Select.
• Les procédures stockées prennent en charge la résolution différée des noms.
• Les procédures stockées sont généralement utilisées pour exécuter une logique métier.
• Les procédures stockées peuvent renvoyer n'importe quel type de données.
• Les procédures stockées peuvent accepter un plus grand nombre de paramètres d'entrée que les fonctions définies par l'utilisateur. Les procédures stockées peuvent avoir jusqu'à 21 000 paramètres d'entrée.
• Les procédures stockées peuvent exécuter du Dynamic SQL.
• Les procédures stockées prennent en charge la gestion des erreurs.
• Les fonctions non déterministes peuvent être utilisées dans les procédures stockées.

---

• Les fonctions définies par l'utilisateur peuvent être utilisées dans les instructions Select.
• Les fonctions définies par l'utilisateur ne prennent pas en charge la résolution différée des noms.
• Les fonctions définies par l'utilisateur sont généralement utilisées pour les calculs.
• Les fonctions définies par l'utilisateur doivent retourner une valeur.
• Les fonctions définies par l'utilisateur ne peuvent pas renvoyer d'images.
• Les fonctions définies par l'utilisateur acceptent un plus petit nombre de paramètres d'entrée que les procédures stockées. Les fonctions définies par l'utilisateur peuvent avoir jusqu'à 1 023 paramètres d'entrée.
• Les tables temporaires ne peuvent pas être utilisées dans les fonctions définies par l'utilisateur.
• Les fonctions définies par l'utilisateur ne peuvent pas exécuter Dynamic SQL.
• Les fonctions définies par l'utilisateur ne prennent pas en charge la gestion des erreurs. RAISEERROR OU @@ERROR ne sont pas autorisés dans les UDF.
• Les fonctions non déterministes ne peuvent pas être utilisées dans les UDF. Par exemple, GETDATE() ne peuvent pas être utilisés dans les UDF.

Answer 5

Écrivez une fonction définie par l'utilisateur lorsque vous souhaitez calculer et renvoyer une valeur à utiliser dans d'autres instructions SQL ; écrivez une procédure stockée lorsque vous souhaitez plutôt regrouper un ensemble éventuellement complexe d'instructions SQL. Il s'agit de deux cas d'utilisation assez différents, après tout !