Création de procédures stockées (moteur de base de données)

Règles de création des procédures stockées

Les règles à suivre pour la création de procédures stockées sont les suivantes :

• La définition CREATE PROCEDURE proprement dite peut comprendre n'importe quel nombre et n'importe quel type d'instructions SQL, à l'exception des instructions suivantes qui ne peuvent être utilisées nulle part au sein d'une procédure stockée.

  CREATE AGGREGATE	CREATE RULE
  CREATE DEFAULT	CREATE SCHEMA
  CREATE ou ALTER FUNCTION	CREATE ou ALTER TRIGGER
  CREATE ou ALTER PROCEDURE	CREATE ou ALTER VIEW
  SET PARSEONLY	SET SHOWPLAN_ALL
  SET SHOWPLAN_TEXT	SET SHOWPLAN_XML
  USE database_name

• D'autres objets de base de données peuvent être créés dans une procédure stockée. Vous pouvez référencer un objet dans la procédure stockée où vous l'avez créé, à condition que sa création précède sa référence.

• Vous pouvez référencer des tables temporaires dans une procédure stockée.

• Si vous créez une table temporaire locale dans une procédure stockée, cette table n'existe que dans le cadre de cette procédure et disparaît au terme de la procédure.

• Si vous exécutez une procédure stockée qui en appelle une autre, la procédure appelée peut accéder à tous les objets créés par la première procédure, y compris aux tables temporaires.

• Si vous exécutez une procédure stockée distante qui apporte des modifications sur une instance distante de Microsoft SQL Server, ces modifications ne pourront pas être restaurées. Les procédures stockées distantes ne font pas partie des transactions.

• Une procédure stockée peut contenir jusqu'à 2 100 paramètres.

• Le nombre maximal de variables locales dans une procédure stockée n'est limité que par la mémoire disponible.

• Selon la mémoire disponible, la taille maximale d'une procédure stockée est de 128 Mo.

Qualification des noms à l'intérieur d'une procédure stockée

Dans une procédure stockée, les noms des objets non qualifiés par un schéma et utilisés dans des instructions (par exemple, SELECT ou INSERT) prennent par défaut le schéma de la procédure stockée. Si un utilisateur qui crée une procédure stockée ne qualifie pas le nom des tables ou vues référencées dans les instructions SELECT, INSERT, UPDATE ou DELETE de la procédure stockée, l'accès à ces tables par le biais de la procédure stockée est limité par défaut au créateur de la procédure.

Les noms d'objets utilisés avec toutes les instructions DLL (Data Definition Language), notamment les instructions CREATE, ALTER ou DROP, les instructions DBCC, les instructions EXECUTE et les instructions SQL dynamiques, doivent être qualifiés avec le nom du schéma de l'objet si d'autres utilisateurs doivent employer la procédure stockée. La spécification du nom du schéma pour ces objets garantit que ce nom correspond au même objet, quel que soit l'appelant de la procédure stockée. Si aucun nom de schéma n'est spécifié, SQL Server tente de résoudre le nom de l'objet en utilisant d'abord le schéma par défaut de l'appelant ou de l'utilisateur spécifié dans la clause EXECUTE AS et ensuite le schéma dbo.

Obscurcissement des définitions de procédure

Pour convertir le texte d'origine de l'instruction CREATE PROCEDURE dans un format obscurci, utilisez l'option WITH ENCRYPTION. Le résultat de l'obscurcissement n'est pas directement visible dans les tables ou vues système dans SQL Server 2008 : les utilisateurs qui n'ont pas accès aux tables système, aux vues système ou aux fichiers de base de données ne peuvent pas récupérer le texte obscurci. Toutefois, le texte est disponible pour les utilisateurs dotés de privilèges et possédant un accès direct aux fichiers de base de données. Ces utilisateurs peuvent être en mesure de procéder à l'ingénierie à rebours de l'obscurcissement afin de récupérer le texte d'origine de la définition de la procédure stockée.

Options de l'instruction SET

Moteur de base de données enregistre les paramètres de SET QUOTED_IDENTIFIER et de SET ANSI_NULLS lors de la création ou de la modification d'une procédure stockée Transact-SQL. Ces paramètres d'origine sont utilisés lors de l'exécution de la procédure stockée. Par conséquent, tous les paramètres d'une session cliente pour SET QUOTED_IDENTIFIER et SET ANSI_NULLS sont ignorés lors de l'exécution de la procédure stockée. Les instructions SET QUOTED_IDENTIFIER et SET ANSI_NULLS qui sont exécutées dans une procédure stockée n'affectent pas la fonctionnalité de cette procédure stockée.

D'autres options SET, telles que SET ARITHABORT, SET ANSI_WARNINGS ou SET ANSI_PADDINGS, ne sont pas sauvegardées lorsqu'une procédure stockée est créée ou modifiée. Si la logique de la procédure stockée dépend d'un paramétrage particulier, incluez une instruction SET au lancement de la procédure pour être certain de la bonne configuration. Lorsqu'une instruction SET est exécutée à partir d'une procédure stockée, les paramètres ne restent effectifs que jusqu'à la fin de l'exécution de la procédure stockée. Les paramètres reprennent ensuite la valeur qu'ils avaient avant l'appel de la procédure stockée. Ceci permet aux clients individuels de définir les options souhaitées sans affecter la logique de la procédure stockée.

Voir aussi

Référence

Autres ressources