• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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

0
fredmastro
Asked:
fredmastro
  • 3
  • 3
1 Solution
 
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
0
 
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?
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fredmastroAuthor Commented:
Thanks :) I've taken care of those, well started the process.  
0
 
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.
0
 
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
0
 
fredmastroAuthor Commented:
Hmm, don't have to loop and it still returns the results I was looking for...

Ok this works. thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now