Créer une procédure stockée à la volée. Quels sont les risques / problèmes?

Pour un système transactionnel, c'est probablement assez cher. Si vous avez un gros travail par lots et que vous voulez utiliser un générateur de code pour une raison quelconque (une architecture assez répandue dans les outils ETL, notamment Oracle Warehouse Builder et Wherescape Red ), il n'est pas déraisonnable de le faire.

Vous avez mentionné que vous appendiez et / ou modifieriez le profil d'appel de la procédure stockée lorsque vous effectuez cette modification. Comment verrouillez-vous le nouveau profil d'appel avec l'application qui appelle? Quel est votre plan de return si jamais vous deviez annuler une modification apscope?

Dans le passé, ce que j'ai fait est juste d'append un suffixe numérique incrémenté au nom de la procédure stockée avec le nouveau profil d'appel – alors vous pouvez modifier l'ancienne version du SP pour appeler le nouveau avec une valeur par défaut pour le paramètre, et alors vous pouvez libérer votre logiciel appelant la nouvelle version.

Si quelque chose casse dans votre nouvelle version et que vous devez revenir en arrière, les appels à l'ancien proc stocké fonctionneront toujours sans erreur, et rempliront simplement les champs personnalisés avec vos valeurs par défaut.

Premièrement, la réponse à cette question dépend vraiment de ce que cette procédure stockée est censée faire exactement. S'il s'agit simplement de lire des données ou de créer un set de résultats pour la création de rapports, cela ne vous dérange pas si c'est un peu incohérent. Si vous faites quelque chose d'intéressant avec vos données, c'est très risqué. Vous devriez penser à savoir s'il est possible (et ce qui se passerait) pour deux users users (ou le même user deux fois) d'exécuter plusieurs versions de la même procédure stockée en même time. Ça sent le train comme une épave. Une option consiste à autoriser uniquement cette modification de procédure lorsque aucun autre user n'est connecté au système, ou de les forcer à quitter la database s'ils le sont. Une autre option consiste à créer votre nouvelle procédure stockée avec un nom légèrement différent et à les échanger lorsque vous estimez que vous pouvez le faire en toute security.

Un autre problème est que l'un des principaux avantages des procédures stockées est que le plan d'exécution est mis en cache, ce qui signifie qu'il s'exécutera plus rapidement. Si vous les créez à la volée, vous perdez cet avantage.

Si vous avez vraiment besoin de faire cela, vous devez randomiser le nom de la procédure pour éviter d'entrer en conflit avec d'autres users. Rappelez-vous toujours que les autres users peuvent faire leur propre chose en même time – la plupart des systèmes de bases de données ne donneront pas une isolation transactionnelle pour les procédures stockées (Postgres est le seul que je connaisse).

Il serait extrêmement rare que ce soit une chose désirable à faire – pourriez-vous élaborer du tout sur ce qui vous a fait choisir cette approche?

Je ne ferais pas ça personnellement.

Comme vous l'avez mentionné, vous aurez besoin de privilèges supplémentaires pour accorder l'access à la création / modification d'objects de database. Cela peut créer un risque de security sérieux car rien n'empêcherait votre application de créer une procédure stockée malveillante si quelqu'un y découvrait un trou de security.

Si votre schéma change, modifiez les procédures stockées avec le schéma.

Vous ne pourrez pas modifier la procédure si un ou plusieurs users exécutent la procédure ou une autre procédure faisant reference à votre procédure. Vous allez bloquer jusqu'à ce que toutes les procédures dépendantes et la procédure que vous souhaitez comstackr (et je pense que la procédure que vous invoquez de votre procédure, mais je ne suis pas certain) ne sont pas utilisées. Cela peut prendre beaucoup de time sur un système de production occupé, et si vous êtes malchanceux, vous pouvez attendre que toutes les dependencies ne soient pas utilisées (5 minutes sur Oracle).
Vous pouvez également entrer dans des situations très laides (j'ai). Prenez par exemple les procédures stockées B et C, qui appellent A, la procédure que vous essayez de comstackr. Lorsque personne n'exécute B, le système verrouille B. Désormais, tout user essayant d'exécuter B se bloque. Le système tente alors de verrouiller C, mais C génère un rapport très long qui ne sera pas fait pendant 10 minutes supplémentaires. Vous allez attendre le locking et certains de vos users auront un système qui ne répondra pas pendant 5 minutes. Mon expérience est avec Oracle, je m'assurerais que votre SGBD cible ne se comporte pas de la même manière, ou qu'il a des échecs plus rapides ou une meilleure stratégie d'acquisition de verrous.
Je suppose que je préviens que ce qui semble fonctionner sur un server de développement peut échouer de façon spectaculaire sur un système de production occupé.

Je ne suis pas sûr que le locking discuté par Tony BanBrahim est vrai dans SQL Server 2005.

J'ai quelques SPs de longue durée (un process batch de 3 heures d'environ 30 sous-process), et j'ai été capable de modifier le SP pendant qu'il fonctionne encore. (Je ne crois pas que les changements prennent effet avant la prochaine exécution, mais cela ne cause aucun blocage ou erreur). Maintenant, le SP externe de longue durée appelle les SP dynamicment avec EXEC et statiquement, mais j'ai changé les SPs root et nested pendant qu'ils s'exécutent sans messages d'erreur ou blocs.

WRT votre question initiale, je pense que votre tactique est bien si elle est utilisée d'une manière contrôlée.

Je ne sais pas avec certitude, mais cela ressemble à un ou deux:

• un problème architectural
• le code existant verrouille-t-il les tables de schéma de l'application?

Je regarderais pour voir quel code verrouille les tables de schéma et réécrit ce code. Avez-vous une tierce partie ou une autre qui verrouille ces tables?