Comprendre la portée des tables temporaires et du SQL dynamique

Une procédure crée une table temporaire avec du SQL dynamique, puis tente de la lire dans l'instruction suivante. L'insertion réussit, mais la lecture signale un objet inconnu. La cause habituelle est la portée : un objet temporaire externe peut être visible à l'intérieur, sans que l'objet créé à l'intérieur survive à la fin de ce contexte.

Donner la propriété au contexte durable

L'exemple crée #OuterWork dans le lot appelant. Le SQL dynamique y insère une ligne et crée sa propre table #InnerWork. Après sa fin, #OuterWork reste lisible. Une autre requête dynamique visant #InnerWork produit l'erreur 208, interceptée par TRY/CATCH.

CREATE TABLE #OuterWork (ItemId int NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'
    INSERT #OuterWork (ItemId) VALUES (@Id);
    CREATE TABLE #InnerWork (ItemId int NOT NULL);
    INSERT #InnerWork VALUES (99);
    SELECT ItemId AS VisibleInside FROM #InnerWork;',
    N'@Id int', @Id = 7;

SELECT ItemId AS VisibleOutside FROM #OuterWork;

BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ItemId FROM #InnerWork;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DROP TABLE #OuterWork;

Le premier résultat contient 99, le deuxième 7 et le dernier décrit l'objet manquant. Aucune seconde connexion n'intervient. La frontière se situe dans une même session ; garder cette connexion ouverte ne prolonge donc pas la table interne.

Si les instructions suivantes ont besoin des données, créez la table avec un schéma explicite dans la portée extérieure. Le lot dynamique peut alors la remplir. Le contrat devient lisible : l'appelant définit la structure, le traitement imbriqué fournit les lignes, puis l'appelant les utilise et supprime l'objet.

Le raisonnement vaut aussi pour les procédures stockées. Une table locale créée par une procédure disparaît à sa fin, même si ses procédures imbriquées peuvent l'utiliser pendant son existence. Une procédure peut exploiter une table créée par son appelant, mais cette dépendance implicite mérite une documentation des colonnes et contraintes attendues.

Séparer valeurs, schémas et connexions

sp_executesql exécute un lot distinct. Les variables scalaires ordinaires de l'appelant n'y sont pas automatiquement disponibles. Transmettez-les comme paramètres typés, comme @Id ici. Cette règle diffère de la visibilité d'une table temporaire déjà existante et évite de concaténer inutilement les valeurs dans du SQL.

Une variable table ne devient pas accessible au SQL dynamique simplement parce que sa déclaration est proche. Un paramètre de type table peut offrir un contrat explicite pour une entrée ensembliste. Pour des résultats intermédiaires modifiables partagés avec le traitement dynamique, une table temporaire externe reste souvent plus simple.

Les noms de colonnes dynamiques constituent un autre problème. Un schéma de sortie qui change à chaque demande complique le SQL statique suivant et le client. Envisagez de retourner directement le résultat dynamique, ou de représenter les attributs variables comme des lignes aux colonnes stables. Validez et délimitez correctement tout identifiant construit.

Une table temporaire locale appartient à une session physique SQL. Deux appels applicatifs ne reçoivent pas forcément la même connexion du pool. Conserver une table pour une requête web ultérieure est donc une stratégie d'état fragile, même si un essai avec un seul utilisateur semble fonctionner.

Éviter un partage involontaire

Remplacer #Work par ##Work crée une table temporaire globale avec d'autres règles de visibilité et de durée. Ce n'est pas seulement prolonger une variable locale. Des demandes concurrentes peuvent partager un nom ou accéder aux mêmes données. Pour un traitement entre requêtes, une table persistante indexée par un identifiant de job unique offre souvent un contrat plus clair.

Évitez aussi les noms identiques dans des contextes imbriqués. Plusieurs objets temporaires locaux homonymes peuvent coexister et rendre la résolution déroutante. Des responsabilités différentes doivent avoir des noms distincts.

Le stockage temporaire n'est pas illimité. Lignes larges, index et sessions longues peuvent immobiliser de l'espace tempdb. Supprimez les gros intermédiaires après leur dernière utilisation, surtout si une procédure continue longtemps. Intégrez également l'effet d'un rollback sur les données insérées.

Testez le véritable chemin applicatif : procédures imbriquées, lots dynamiques, erreurs et connexions séparées du pool. La correction utile consiste à définir propriétaire et durée de vie. L'erreur d'objet devient alors prévisible plutôt qu'apparemment intermittente.

Références techniques: Microsoft Learn: CREATE TABLE and temporary scope · Microsoft Learn: sp_executesql.