Fonction vs. procédure stockée dans le serveur SQL

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

+---------------------------------+----------------------------------------+
| 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.    |                                        |
+---------------------------------+----------------------------------------+

et les procédures stockées servent à des fins distinctes. Bien que ce ne soit pas la meilleure analogie, les fonctions peuvent être vues littéralement comme n'importe quelle autre fonction que vous utiliseriez dans n'importe quel langage de programmation, mais les procs stockés sont plus comme des programmes individuels ou un script de fournée.

ont normalement une sortie et des entrées facultatives. La sortie peut alors être utilisée comme entrée vers une autre fonction (un serveur SQL intégré tel que DATEDIFF, LEN, etc.) ou comme prédicat vers un SQL. Requête-par exemple, SELECT a, b, dbo.MyFunction(c) FROM table ou SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

procs stockés sont utilisés pour lier des requêtes SQL ensemble dans une transaction, et l'interface avec le monde extérieur. Des cadres tels que: ADO.NET, etc. ne peut pas appeler une fonction directement, mais ils peuvent faire appel à une procédure stockée directement.

fonctions ont un danger caché cependant: ils peuvent être utilisés abusivement et causer des problèmes de performance assez désagréables: considérez cette requête:

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

Où MyFunction est déclaré comme suit:

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 est que la fonction MyFunction est appelée pour chaque ligne dans la table MyTable. Si MyTable a 1000 lignes, alors c'est un autre 1000 requêtes ad-hoc contre la base de données. De même, si la fonction est appelée lorsqu'elle est spécifiée dans la colonne spec, alors la fonction sera appelée pour chaque ligne retournée par SELECT.

donc vous devez être prudent fonctions d'écriture. Si vous choisissez d'un tableau dans un fonction, vous devez vous demander si elle peut être mieux effectuée avec une jointure dans le parent stocké proc ou une autre construction SQL (comme CASE ... LORSQUE. .. AUTRE. .. FIN.)

Écrire une fonction définie par l'utilisateur lorsque vous voulez calculer et retourner une valeur pour l'utiliser dans d'autres instructions SQL; écrire une procédure stockée lorsque vous souhaitez plutôt de groupe, éventuellement, un complexe jeu d'instructions SQL. Ce sont deux cas d'utilisation assez différents, après tout!

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 des déclarations sélectionnées.
• les procédures stockées supportent la résolution différée du nom.
Les procédures stockées
• sont généralement utilisées pour exécuter la logique commerciale.
• procédures stockées peuvent retourner n'importe quel type de données.
• procédures stockées peuvent accepter un plus grand nombre de paramètre d'entrée de fonctions définies par l'utilisateur. Les procédures stockées peuvent avoir jusqu'à 21 000 paramètres d'entrée.
• procédures stockées peuvent exécuter dynamique SQL.
• procédures stockées support error handling.
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 du nom.
• 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 retourner 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. UDFs peut avoir jusqu'à 1.023 paramètres d'entrée.
• tables temporaires ne peut pas être utilisé dans des fonctions définies par l'utilisateur.
• les fonctions définies par L'utilisateur ne peuvent pas exécuter de SQL dynamique.
Les fonctions
• définies par L'utilisateur ne prennent pas en charge le traitement des erreurs. RAISEERROR ou @@ERROR ne sont pas autorisés dans les UDFs.
• les fonctions non déterministes ne peuvent pas être utilisées dans les UDFs. Par exemple, GETDATE() ne peut pas être utilisé dans les UDFs.

Différence De Base

doit renvoyer une valeur mais dans la procédure stockée elle est optionnelle( la procédure peut renvoyer des valeurs zéro ou n).

ne peuvent avoir que des paramètres d'entrée alors que les procédures peuvent avoir des paramètres d'entrée/sortie .

fonction prend un paramètre d'entrée il est obligatoire, mais procédure stockée peut prendre o À n Paramètres d'entrée..

fonctions peuvent être appelé de la procédure alors que les procédures ne peuvent pas être appelé de la fonction.

Écart D'Avance

permet aussi bien SELECT que DML(INSERT/UPDATE/DELETE) statement alors que la fonction ne permet que SELECT statement.

ne peuvent pas être utilisées dans une instruction SELECT alors que la fonction peut être intégrée dans une instruction SELECT.

procédures stockées ne peuvent pas être utilisé dans les instructions SQL n'importe où dans la section Où/avoir/sélectionner alors que la fonction peut l'être.

fonctions qui renvoient des tables peuvent être traitées comme un autre ensemble de lignes. Ceci peut être utilisé dans les jointures avec d'autres tables.

fonction en ligne peut être considérée comme des vues qui prennent des paramètres et peut être utilisé dans les jointures et D'autres opérations de Rowset.

Exception peuvent être traitées par bloc try-catch dans une Procédure considérant que le bloc try-catch ne peut pas être utilisé dans une Fonction.

nous pouvons aller pour la gestion des transactions dans la procédure alors que nous ne pouvons pas aller dans la fonction.

source

une fonction définie par L'utilisateur est un outil important Disponible pour un programmeur de serveur sql. Vous pouvez l'utiliser en ligne dans une instruction SQL comme so

SELECT a, lookupValue(b), c FROM customers 

où lookupValue sera un UDF. Ce type de fonctionnalité n'est pas possible lors de l'utilisation d'une procédure stockée. En même temps, vous ne pouvez pas faire certaines choses à l'intérieur d'un UDF. La chose fondamentale à se rappeler ici est que UDF:

• ne peut pas créer de changements permanents
• ne peut pas changer les données

une procédure stockée peut faire ces choses.

pour moi, l'usage en ligne d'un UDF est l'usage le plus important d'un UDF.

Procédures Stockées sont utilisés comme des scripts . Ils exécuter une série de commandes pour vous et vous pouvez planifier l'exécution à certains moments.

fonctions sont utilisées comme méthodes. vous lui passez quelque chose et il renvoie un résultat. Devrait être petit et rapide - le fait à la volée.

procédure stockée:

• est comme un programme miniature dans SQL Server.
• peut être aussi simple qu'un select statement, ou aussi complexe qu'un long script qui ajoute, supprime, met à jour et / ou lit des données à partir de multiples les tables dans une base de données.
• (peut mettre en œuvre des boucles et des curseurs, qui vous permettent à la fois de travailler avec des résultats plus petits ou des opérations ligne par ligne sur les données.)
• doit être appelé en utilisant EXEC ou EXECUTE .
• renvoie des variables de table, mais nous ne pouvons pas utiliser le paramètre OUT .
• supporte les transactions.

fonction:

• ne peut pas être utilisé pour mettre à jour, supprimer ou ajouter des enregistrements à la base de données.
• renvoie simplement une valeur unique ou une valeur de table.

• ne peut être utilisé que pour sélectionner dossier. Cependant, il peut être appelé très facilement à partir de la norme SQL, comme:

  SELECT dbo.functionname('Parameter1')
  

  ou

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• pour les opérations de sélection simples réutilisables, les fonctions peuvent simplifier le code. Méfiez-vous simplement de l'utilisation des clauses JOIN dans vos fonctions. Si votre fonction a une clause JOIN et vous l'appelez d'un autre select déclaration qui retourne des résultats multiples, que l'appel de fonction sera JOIN ceux tables pour chaque ligne retournées dans le jeu de résultats. Si bien qu'ils puissent être utiles pour simplifier une certaine logique, ils peuvent également être performance goulot d'étranglement si elles ne sont pas utilisées correctement.

• renvoie les valeurs en utilisant le paramètre OUT .
• ne supporte pas les transactions.

Pour déterminer quand utiliser quoi les points suivants peuvent aider-

1. procédures stockées ne peuvent pas retourner une variable de table où que la fonction peut le faire.

2. vous pouvez utiliser des procédures stockées pour modifier les paramètres d'environnement du serveur lorsque vous utilisez des fonctions que vous ne pouvez pas.

cheers

les fonctions de serveur SQL, comme les curseurs, sont destinées à être utilisées comme dernière arme! Ils ont des problèmes de rendement et, par conséquent, l'utilisation d'une fonction de valeur de tableau devrait être évitée autant que possible. Parler de performance, c'est parler d'une table avec plus de 1.000.000 d'enregistrements hébergés sur un serveur sur un matériel de classe moyenne; sinon, vous n'avez pas besoin de vous soucier de l'impact de performance causé par les fonctions.

1. N'utilisez jamais une fonction pour retourner un résultat-défini à un code externe (comme ADO.Net)
2. utilisez la combinaison vues/procs stocké autant que possible. vous pouvez vous remettre des problèmes futurs de croissance-performance en utilisant les suggestions DTA (Database Tuning Adviser) vous donnerait (comme des vues indexées et des statistiques) -- parfois!

pour de plus amples informations, voir: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
   temporary tables                    |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                           statements.

commence avec des fonctions qui renvoient une seule valeur. La bonne chose est que vous pouvez mettre le code fréquemment utilisé dans une fonction et les retourner comme une colonne dans un ensemble de résultats.

Alors, vous pourriez utiliser une fonction pour paramétrée liste des villes. dbo.GetCitiesIn ("NY") qui renvoie une table qui peut être utilisée comme jointure.

C'est une façon d'organiser le code. De savoir quand quelque chose est réutilisable et quand c'est une perte de temps est quelque chose de gagné par essai et erreur et de l'expérience.

aussi, les fonctions sont une bonne idée dans SQL Server. Ils sont plus rapides et peuvent être assez puissant. Sélection en ligne et directe. Attention à ne pas abuser.

• il est obligatoire pour la fonction de retourner une valeur alors qu'il ne s'agit pas d'une procédure stockée.
• sélectionner les déclarations acceptées uniquement en UDF alors que les déclarations DML ne sont pas requises.
La procédure stockée
• accepte toutes les déclarations ainsi que les déclarations DML.
• UDF permet uniquement les entrées et non les sorties.
• procédure Stockée permet à la fois des entrées et des sorties.
• blocs Catch ne peut pas être utilisé dans L'UDF mais peut être utilisé dans la procédure stockée.
• aucune transaction autorisée dans les fonctions UDF mais dans les procédures stockées ils sont autorisés.
• seules les variables de table peuvent être utilisées dans les tables UDF et non temporaires.
• procédure stockée permet à la fois des variables de table et des tables temporaires.
• UDF ne permet pas d'appeler les procédures stockées à partir de fonctions alors que les procédures stockées permettent d'appeler des fonctions.
• UDF est utilisé dans la clause join tandis que les procédures stockées ne peuvent pas être utilisées dans la clause join.
La procédure stockée
• permettra toujours un retour à zéro. UDF, au contraire, a des valeurs qui doivent revenir à un point prédéterminé.

Voici une raison pratique pour préférer les fonctions aux procédures stockées. Si vous avez une procédure stockée qui a besoin des résultats d'une autre procédure stockée, vous devez utiliser un insert-instruction exec. Cela signifie que vous devez créer une table temporaire et l'utilisation d'un exec déclaration d'insérer les résultats de la procédure stockée dans la table temporaire. C'est salissant. Un problème est que insert-execs ne peut pas être imbriqué .

si vous êtes coincé avec des procédures stockées qui appeler d'autres procédures stockées, vous pouvez exécuter ce. Si la procédure stockée imbriquée retourne simplement un jeu de données, il peut être remplacé par une fonction table et vous n'aurez plus cette erreur.

( c'est une autre raison pour laquelle nous devrions exclure la logique commerciale de la base de données )

• les fonctions peuvent être utilisées dans une instruction select lorsque les procédures ne le peuvent pas.

• procédure stockée prend à la fois les paramètres d'entrée et de sortie, mais les fonctions ne prend que les paramètres d'entrée.

• les fonctions ne peuvent pas retourner les valeurs de type texte, ntext, image et horodatage où les procédures peuvent.

• les fonctions peuvent être utilisées comme utilisateur les types de données définis dans la table create mais les procédures ne peuvent pas.

***par exemple:-créer des table <tablename>(name varchar(10),salary getsal(name))

ici getsal est une fonction définie par l'utilisateur qui renvoie un type de salaire, lorsque la table est créée, aucun stockage n'est attribué pour le type de salaire, et la fonction getsal n'est pas non plus exécutée, mais lorsque nous récupérons certaines valeurs de cette table, la fonction getsal est exécutée et le retour Le Type renvoyé est le jeu de résultats.

dans SQL Server, les fonctions et les procédures stockées sont deux types différents d'entités.

fonction: dans la base de données SQL Server, les fonctions sont utilisées pour effectuer certaines actions et l'action renvoie un résultat immédiatement. Les fonctions sont de deux types:

1. système défini

2. défini par L'utilisateur

procédures stockées: dans SQL Server, les procédures stockées sont stockées dans le serveur et il peut être des valeurs zéro, simple et multiple. Les procédures stockées sont de deux types:

1. Procédures Stockées Système
2. Défini Par L'Utilisateur Des Procédures