Constructing a result set from two dynamically created tables in a stored procedure

See more:

SQL-Server-2005

SQL

SQL-Server

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

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