从存储过程中的两个动态创建的表构造结果集 [英] Constructing a result set from two dynamically created tables in a stored procedure

问题描述

大家好，

我正在存储过程中动态填写两个表，并希望从每个表中提取出我想要的字段，这些字段将作为结果集成为一个表.

从结构上讲，存储过程类似于以下伪代码:

Hallo everybody,

I am dynamically filling out two tables in my stored procedure and wished to have extract out the fields those I want from each table which will become a table as a result set.

Structurally the stored procedure looks like the following pseudocode:

Declare Table_1 Table (F1, ...)
Insert InTo Table_1 Select * From X.Table

Declare Table_2 Table (F1, ...)
Insert InTo Table_2 Select * From Y.Table

Select * From Table_1, Table_2

现在，这里的最大问题是，即使其中有一个有效值，select语句也无法获取第二个表中找到的字段，这仅在第一个表中没有值时才会发生.换句话说，如果第一个表具有值，那么select语句将返回结果集；否则，如果第一个表没有值，但如果第二个表具有值，则select语句将不返回结果集.

顺便说一句，我正在使用SQL2005服务器.

如果我不清楚，请告诉我.

Now, the big problem here is that the select statement cannot fetch out fields those are found in the second table even if there is a valid value in it this happens only when there is no value in the first table. In other words, if the first table has value then the select statement returns a result set otherwise if the first table doesn''t have value but if the second table has then the select statement is not returning a result set.

By the way, I am using SQL2005 server.

If I am not clear, please let me know.

Thanks

推荐答案

如果表的列结构相同(甚至相似)，则可以在两个表之间进行UNION ，并且应该可以返回所有表结果在一起.

如果表结构完全不同(或者您不想执行UNION)，则可以创建一个临时表，并将第一个和第二个表的值插入其中.

select 并返回此临时表中的所有值.

If the table column structures are same (or even similar), you can do a UNION between the two tables and you should be able to return all the results together.

If the table structures are drastically different (or you do not want to do a UNION), you can create a temporary table and insert the values of both the first and the second tables into it.

The select and return all the values from this temporary table.

您可以使用 ^ ]，当第一个表中没有链接记录时，也可以从第二个表中获取记录.

You could use a FULL JOIN[^] to also get the records from the second table when no linked record exists with the first.

这篇关于从存储过程中的两个动态创建的表构造结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！