T-SQL Dynamic SQL and Temp Tables

il semble que les # temptables créés en utilisant le SQL dynamique via la méthode EXECUTE string ont une portée différente et ne peuvent pas être référencés par des SQLs "fixes" dans la même procédure stockée. Cependant, je peux faire référence à une table temp créée par une déclaration SQL dynamique dans un SQL dynamique subséquent mais il semble qu'une procédure stockée ne renvoie pas un résultat de requête à un client appelant à moins que le SQL ne soit fixe.

un scénario simple à 2 tables: J'ai 2 tables. Appelons-les Commandes et les Éléments. Order a une clé primaire de OrderId et Items a une clé primaire de ItemId. Article.OrderId est la clé étrangère pour identifier l'ordre parent. Une commande peut contenir de 1 à N.

je veux être capable de fournir une interface de type "query builder" très flexible pour permettre à l'utilisateur de sélectionner les éléments qu'il veut voir. Les critères de filtrage peuvent être basés sur les champs de la table Items et/ou de la table commande parent. Si un article satisfait à la condition de filtre y compris et condition sur le ordre parent s'il en existe un, l'élément doit être retourné dans la requête ainsi que l'ordre parent.

Habituellement, je suppose, la plupart des gens construisent une jointure entre la table D'Item et les tables d'ordre parent. Je voudrais effectuer 2 requêtes distinctes à la place. Un pour retourner tous les articles admissibles et l'autre pour retourner toutes les commandes mères distinctes. La raison est double et vous pouvez ou non être d'accord.

la première raison est que je dois interroger tous les colonnes dans la table d'ordre de parent et si je faisais une seule requête pour joindre la table D'ordres à la table D'Articles, je serais repoeating l'information D'Ordre plusieurs fois. Comme il y a généralement un grand nombre d'articles par commande, j'aimerais éviter cela parce que cela entraînerait beaucoup plus de données étant transférées à un client gros. Au lieu de cela, comme mentionné, je voudrais retourner les deux tables individuellement dans un ensemble de données et d'utiliser les deux tables à l'intérieur pour peupler une commande personnalisée et les enfants articles client objet. (Je ne connais pas encore assez LINQ ou Entity Framework. Je construis mes objets à la main). La deuxième raison pour laquelle je voudrais retourner deux tables au lieu d'une est parce que j'ai déjà une autre procédure qui retourne tous les articles pour un OrderId donné avec L'ordre parent et je voudrais utiliser la même approche de 2 tables afin que je puisse réutiliser le code client pour peupler mon ordre personnalisé et les objets Client à partir des 2 datatables retournés.

Ce que j'espérais, à faire était de ceci:

construisez une chaîne SQL dynamique sur le Client qui relie la table orders à la table Items et filtres appropriés sur chaque table comme spécifié par le filtre personnalisé créé sur L'application Winform fat-client. La construction SQL sur le client aurait ressemblé à quelque chose comme ceci:

TempSQL = "

    INSERT INTO #ItemsToQuery
       OrderId, ItemsId
    FROM
       Orders, Items 
    WHERE
       Orders.OrderID = Items.OrderId AND
       /* Some unpredictable Order filters go here */
      AND
       /* Some unpredictable Items filters go here */
    "

Ensuite, je voudrais appeler une procédure stockée,

CREATE PROCEDURE GetItemsAndOrders(@tempSql as text)
   Execute (@tempSQL) --to create the #ItemsToQuery table

SELECT * FROM Items WHERE Items.ItemId IN (SELECT ItemId FROM #ItemsToQuery)

SELECT * FROM Orders WHERE Orders.OrderId IN (SELECT DISTINCT OrderId FROM #ItemsToQuery)

le problème avec cette approche est que la table # ItemsToQuery, puisqu'elle a été créée par dynamic SQL, est inaccessible à partir des 2 SQL statiques suivants et si je change les SQL statiques en dynamique, aucun résultat n'est transmis au client fat.

3 autour de vous viennent à l'esprit mais je suis de regarder pour une meilleure:

1) le premier SQL peut être exécuté en exécutant le SQL dynamiquement construit à partir du client. Les résultats pourraient ensuite être transmis sous forme de tableau à une version modifiée de la procédure stockée ci-dessus. Je suis familier avec la transmission de données de table comme XML. Si je l'ai fait, le proc stocké pourrait alors insérer les données dans une table temporaire en utilisant un SQL statique qui, parce qu'il a été créé par SQL dynamique, pourrait alors être interrogé sans problème. (Je pourrais aussi étudier en passant le nouveau type de Table param au lieu de XML.) Cependant, je voudrais éviter de passer des listes potentiellement importantes à une procédure stockée.

2) je pourrais effectuer toutes les requêtes du client.

La première serait quelque chose comme ceci:

SELECT Items.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)
SELECT Orders.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)

ceci me fournit toujours avec la possibilité de réutiliser mon code objet-population côté client parce que les commandes et les articles continuent d'être retournés dans deux tableaux différents.

j'ai un sentiment que je pourrais avoir quelques options à l'aide d'un Tableau de type de données dans ma procédure stockée, mais qui est aussi nouveau pour moi et j'apprécierais un peu d'alimentation à la cuillère.

si vous avez même scanné si loin dans ce que j'ai écrit, je suis surpris, mais si c'est le cas, j'apprécierais n'importe quelle de vos pensées sur la façon de accomplissez ce mieux.