Dynamic SELECT UNION ALL in a SP

Hello,

I'm trying to create a dynamic SELECT .... UNION ALL SELECT ....
statement.

Problem is I don't know how many SELECT statements I will have to union.  I have a stored proc I created that will create a CURSOR and go through each record.

For Each Record I want to add on to my SELECT, a UNION ALL SELECT.... and then run it at the end.

But I'm having trouble figuring out the proper flow control for this.

So far this is what I have, but it only returns one result set, if I run it on it's own I get two result sets.  So how can I loop through the WHILE statement and build a dynamic union between them all?


DECLARE @AuthID Int
DECLARE @RowNum Int
DECLARE @AuthCount Int
DECLARE curAuthor CURSOR
      FOR
      SELECT A.Author_ID FROM tblAuthor As A WHERE A.Group_ID IN (1, 8, 9 ,10) AND A.Author_ID <> 1
OPEN curAuthor
FETCH NEXT FROM curAuthor INTO @AuthID
SET @RowNum = 0
SET @AuthCount = (SELECT Count(A.Author_ID) FROM tblAuthor As A WHERE A.Group_ID IN (1, 8, 9 ,10) AND A.Author_ID <> 1)
WHILE @@FETCH_STATUS = 0
      BEGIN
            SELECT
            (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = @AuthID AND PV.Field_ID = 5
            ) AS 'Rank',
            (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = @AuthID AND PV.Field_ID = 4
            ) AS 'Name',
            (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = @AuthID AND PV.Field_ID = 1
            ) AS 'Class',
            (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = @AuthID AND PV.Field_ID = 2
            ) AS 'Level',
            (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = @AuthID AND PV.Field_ID = 3
            ) AS 'Notes'
            FETCH NEXT FROM curAuthor INTO @AuthID
      END
CLOSE curAuthor
DEALLOCATE curAuthor

LVL 2
fredmastroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Why don't you tell us what you are trying to achieve and I stronly suspect any solution offered, will not have to resort to using CURSORs, Dynamic SQL and even posiibly UNION.

Also, the following questions are considered abandoned, please attend to them:
1 01/03/2006 200 Dynamic DataGrid, Add Col, Cast Errors?  Open ASP.NET
2 01/03/2006 75 DataGrid/Adapater Interpeting Field as w...  Open ASP.NET
fredmastroAuthor Commented:
As far as the adbandond questions, #2 I answered myself and I stated so in the question.  Don't want to delete it because someone else could use the answer.  
#1 no one could understand what I was saying, and I just "closed" it officaly.


Let me simplfy my question.

Say I get a count of records, say 5.

Now I want to loop through and create a SELECT statement like so:
SELECT Name FROM Table WHERE ID = 1
SELECT Name FROM Table WHERE ID = 2
SELECT Name FROM Table WHERE ID = 3
SELECT Name FROM Table WHERE ID = 4
SELECT Name FROM Table WHERE ID = 5


If I run this normally I get back 5 result sets.  If I run from a stored proc I get back only the first result.

So now the #5 might gbe 5 today but it might be 4 tomorrow or another number, so I never know, I loop until I'm done.

So how can I, in the Stored Proc create this.... ???

SELECT Name FROM Table WHERE ID = 1
UNION ALL
SELECT Name FROM Table WHERE ID = 2
UNION ALL
SELECT Name FROM Table WHERE ID = 3
UNION ALL
SELECT Name FROM Table WHERE ID = 4
UNION ALL
SELECT Name FROM Table WHERE ID = 5


Then run it ALL TOGETHER and return 1 result set with all the data?
Anthony PerkinsCommented:
>>As far as the adbandond questions, #2 I answered myself and I stated so in the question.  Don't want to delete it because someone else could use the answer.  <<
Than post a message in Community Support to that effect, make sure to include a link to the thread.  See here from the EE Help:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70

>>#1 no one could understand what I was saying, and I just "closed" it officaly.<<
Again post a message in CS to that effect.  See here from the EE Help:

Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

fredmastroAuthor Commented:
Thanks :) I've taken care of those, well started the process.  
Anthony PerkinsCommented:
>>If I run this normally I get back 5 result sets.  If I run from a stored proc I get back only the first result.<<
Let me correct that misconception:  You actually get 5 resultsets returned.  You can clearly see this in SQL Query Analyzer.

If all you wanted to do was this:

SELECT Name FROM Table WHERE ID = 1
UNION ALL
SELECT Name FROM Table WHERE ID = 2
UNION ALL
SELECT Name FROM Table WHERE ID = 3
UNION ALL
SELECT Name FROM Table WHERE ID = 4
UNION ALL
SELECT Name FROM Table WHERE ID = 5

Than you might find the foloowing a tad more efficient and easier to code :)

SELECT Name FROM Table WHERE ID <= 5

However, I suspect from your original question what you are attempting is somewhat more complex.
knight908Commented:
Don't need to use a cursor...here is a much faster example of how to do this:

Select
      MyAuthorTbl.Author_id,
      (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = MyAuthorTbl.Author_id AND PV.Field_ID = 5
          ) AS 'Rank',
          (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = MyAuthorTbl.Author_id AND PV.Field_ID = 4
          ) AS 'Name',
          (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = MyAuthorTbl.Author_id AND PV.Field_ID = 1
          ) AS 'Class',
          (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = MyAuthorTbl.Author_id AND PV.Field_ID = 2
          ) AS 'Level',
          (SELECT TOP 1 PV.Value FROM tblProfileValues AS PV WHERE PV.Author_ID = MyAuthorTbl.Author_id AND PV.Field_ID = 3
          ) AS 'Notes'
      from
            (Select author_id from tblAuthor WHERE Group_ID IN (1, 8, 9 ,10) AND Author_ID <> 1) MyAuthorTbl

=========================
By limiting the search to just pull the author IDs you need in your custom "MyAuthorTbl" it should be faster and still give you the results you need

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fredmastroAuthor Commented:
Hmm, don't have to loop and it still returns the results I was looking for...

Ok this works. thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.