Off the bat, a union might not work because the number of may be different. But, to union two selects, you need the union keyword between the statments.

A union is not approproprate in your circumstance.

The code in your scenario is yet another reason why stored procs are a better - more manageable way to implement things:


You have a few basic options:


First, run from a stored proc - passing in the parameters you need. In a proc, you could run both statements - return 2 result sets. Depending on what you need to do - this actually may be a good idea.

Second, Same as number 1 - but you create a temp table and a cursor for each intermediate result set. Then, you fetch the data from the cursors and insert into the temp table. The last thing you do is return the data from the temp table - and then dropping the temp table. This only becomes an option if your correlated subquery performance is bad (which I doubt).

Third, and perhaps the best - run a correlated subquery for each column of data you have to return. The sql becomes to look messy - but it will work. If you want to clean it up a bit, write a UDF in SQL Server.