?
Solved

Dynamic SELECT UNION ALL in a SP

Posted on 2006-04-09
7
Medium Priority
?
331 Views
Last Modified: 2012-08-13
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
Comment
Question by:fredmastro
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16412532
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
 
LVL 2

Author Comment

by:fredmastro
ID: 16412568
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16412592
>>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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:fredmastro
ID: 16412609
Thanks :) I've taken care of those, well started the process.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16412615
>>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
 

Accepted Solution

by:
knight908 earned 400 total points
ID: 16412648
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
 
LVL 2

Author Comment

by:fredmastro
ID: 16412660
Hmm, don't have to loop and it still returns the results I was looking for...

Ok this works. thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question