Fonctions et procédures stockées

Question

Imaginons que je doive implémenter un morceau de code T-SQL qui doit retourner une table comme résultat. Je peux implémenter une fonction à valeur de tableau ou bien une procédure stockée qui renvoie un ensemble de lignes. Que dois-je utiliser ?

En bref, ce que je veux savoir c'est :

Quelles sont les principales différences entre les fonctions et les procédures stockées ? Quelles considérations dois-je prendre en compte pour utiliser l'un ou l'autre ?

Answer 1

Si vous êtes susceptible de vouloir combiner le résultat de ce morceau de code avec d'autres tables, il est évident qu'une fonction à valeur de table vous permettra de composer les résultats dans une seule instruction SELECT.

En général, il y a une hiérarchie (Vue < Fonction TV < Procédure stockée). Vous pouvez faire plus dans chacun d'entre eux, mais la capacité à composer les sorties, et pour l'optimiseur d'être vraiment impliqué, diminue à mesure que la fonctionnalité augmente.

Utilisez donc celui qui vous permet le moins d'exprimer le résultat souhaité.

Answer 2

Les fonctions doivent être déterministes et ne peuvent pas être utilisées pour apporter des modifications à la base de données, alors que les procédures stockées vous permettent d'effectuer des insertions et des mises à jour, etc.

Vous devriez limiter votre utilisation des fonctions, car elles posent un énorme problème d'évolutivité pour les requêtes complexes et volumineuses. Elles deviennent en quelque sorte une "boîte noire" pour l'optimiseur de requêtes, et vous constaterez d'énormes différences de performances entre l'utilisation de fonctions et la simple insertion du code dans une requête.

Mais ils sont définitivement utiles pour les rendements évalués par table dans des cas très spécifiques.

Si vous devez analyser une liste délimitée par des virgules, pour simuler le passage d'un tableau à une procédure, une fonction peut transformer la liste en tableau pour vous. C'est une pratique courante avec Sql Server 2005, puisque nous ne pouvons pas encore passer des tableaux aux procédures stockées (nous le pouvons avec 2008).

Answer 3

Extrait de la documentation :

Si une procédure stockée répond aux critères suivants, elle est un bon candidat pour être réécrite comme une fonction à valeur de table :

• La logique peut être exprimée dans une seule instruction SELECT, mais il s'agit d'une procédure stockée, plutôt que d'une vue, uniquement en raison du besoin de paramètres.

• La procédure stockée n'effectue pas d'opérations de mise à jour, sauf pour les variables de la table.

• Il n'y a pas besoin d'instructions EXECUTE dynamiques.

• La procédure stockée renvoie un jeu de résultats.

• L'objectif principal de la procédure stockée est de construire des résultats intermédiaires qui seront chargés dans une table temporaire, qui sera ensuite interrogée dans une instruction SELECT.

Answer 4

Je vais écrire quelques différences intéressantes entre les procédures stockées et les fonctions.

• On peut utiliser des fonctions dans les requêtes sélectives, mais pas les procédures procédures stockées dans les requêtes sélectives.

• Nous ne pouvons pas utiliser de fonctions non déterministes dans Functions mais nous pouvons mais nous pouvons utiliser des fonctions non déterministes dans des procédures stockées. La question qui se pose alors est la suivante : qu'est-ce qu'une fonction non déterministe ? La réponse est:-

  Une fonction non déterministe est une fonction qui renvoie des sorties différentes pour les mêmes valeurs d'entrée à des moments différents, comme getdate(). Elle renvoie toujours une valeur différente à chaque fois qu'elle est exécutée.

  Exception:-

  Les versions antérieures de sql server avant sql 2000 ne permettent pas d'utiliser la fonction getdate() dans les fonctions définies par l'utilisateur, mais la version 2005 et les suivantes nous permettent d'utiliser la fonction getdate() dans une fonction définie par l'utilisateur.

  Newid() est un autre exemple de fonction non déterministe mais ne peut pas être utilisé dans les fonctions définies par l'utilisateur mais nous pouvons l'utiliser dans une procédure stockée.

• Nous pouvons utiliser des instructions DML (insert, update, delete) dans une procédure stockée procédure stockée, mais nous ne pouvons pas utiliser les instructions DML dans des ou des tables permanentes. Si nous voulons faire des opérations DML dans des fonctions, nous pouvons le faire sur des variables de table, pas sur des tables permanentes.

• Nous ne pouvons pas utiliser la gestion des erreurs dans les fonctions, mais nous pouvons le faire dans les procédures stockées. dans les procédures stockées.

Answer 5

1. La procédure peut renvoyer zéro ou n valeurs alors que la fonction peut renvoyer une valeur qui est obligatoire.

2. Les procédures peuvent avoir des paramètres d'entrée et de sortie, alors que les fonctions ne peuvent avoir que des paramètres d'entrée.

3. Une procédure peut contenir des instructions de sélection ainsi que des instructions DML, alors qu'une fonction ne peut contenir que des instructions de sélection.

4. Les fonctions peuvent être appelées à partir d'une procédure, alors que les procédures ne peuvent pas être appelées à partir d'une fonction. appelées depuis une fonction.

5. Les exceptions peuvent être traitées par le bloc try-catch dans une procédure alors que le bloc try-catch ne peut pas être utilisé dans une fonction.

6. Nous pouvons gérer les transactions dans les procédures alors que nous ne pouvons pas le faire dans les fonctions.

7. Les procédures ne peuvent pas être utilisées dans une instruction de sélection alors que les fonctions peuvent être intégrées dans une instruction de sélection.

8. L'UDF (User Defined function) peut être utilisée dans les instructions SQL à n'importe quel endroit de l'arborescence. WHERE / HAVING / SELECT alors que les procédures stockées ne le sont pas.

9. Les UDF qui renvoient des tableaux peuvent être traités comme un autre rowset. Ceci peut être utilisé dans JOIN avec d'autres tables.

10. Les UDF en ligne peuvent être considérés comme des vues qui prennent des paramètres et qui peuvent être utilisées dans les éléments suivants JOIN et d'autres opérations de rowset.