UNION operations within a WHILE loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

UNION operations within a WHILE loop

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-12 : 09:29:24

Andy writes "Hi there,

Another post to try and stump you guys, I'm hoping you'll be able to crack it. I'm creating a system that uses FOR XML EXPLICIT to return XML to the client. The problem arises when I try and use this in conjunction with cursors to create a complex XML document.

Because the query requires has to be of the form...

SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3 WHERE ID = @ID
FOR XML EXPLICIT

... I can't seem to find an efficient way of looping through a variety of @ID values using a cursor. What I would like to do is below, but the syntax is invalid.

SELECT * FROM table1
UNION
SELECT * FROM table2
FETCH NEXT FROM tempTable INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
UNION
SELECT * FROM table3 WHERE ID = @ID
FETCH NEXT FROM tempTable INTO @ID
END
FOR XML EXPLICIT

This however doesn't work, as the syntax is invalid. I have created a work round which uses the loop to create a string and use the EXEC command to then run the query (i.e. a huge dynamic SQL statement). This is an unnefficient way of doing it I know, but I can't seem to find any other way of doing it.

Hope you guys have as much fun trying to solve this one as I do

Thanks

Andy M"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 09:34:37

Well, it'll be a hell of a lot easier for us to help if you provide the table structures and a sample of the XML output you want. Without it I can't even guess where to begin.

AFAIK the EXPLICIT syntax can do some very advanced formatting of the XML output. Look in Books Online under "EXPLICIT" or "EXPLICIT mode". There are some examples there. None of them use cursors.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-12 : 12:00:19

would some form of the following help....

SELECT * FROM (SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3 WHERE ID = @ID) as a

that way it'll be treated as 1 recordset?

I can't see anything in BOL under EXPLICIT that relates to XML....including "for xml explicit" in QA fails for me (v7) so i'm not sure how you're getting it to work.....i presume thus it's a feature of sql2000.

andymaule
Starting Member

6 Posts

Posted - 2002-07-15 : 04:46:44

quote:

Well, it'll be a hell of a lot easier for us to help if you provide the table structures and a sample of the XML output you want. Without it I can't even guess where to begin.

AFAIK the EXPLICIT syntax can do some very advanced formatting of the XML output. Look in Books Online under "EXPLICIT" or "EXPLICIT mode". There are some examples there. None of them use cursors.

Thanks for that, but I can't really post the whole table structure and xml doc because they are both huge... and besides you don't need it. The only thing I want to know is how to create a union operator to do an FOR XML EXPLICIT stored proc using cursors. I'm using about twenty other FOR XML EXPLICIT stored procs in the DB and they all work fine, and I understand the explicit syntax... just not sure how I can make n amount of unions using a cursor without writing dynamic SQL

andymaule
Starting Member

6 Posts

Posted - 2002-07-15 : 04:48:56

quote:

would some form of the following help....

SELECT * FROM (SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3 WHERE ID = @ID) as a

that way it'll be treated as 1 recordset?

I can't see anything in BOL under EXPLICIT that relates to XML....including "for xml explicit" in QA fails for me (v7) so i'm not sure how you're getting it to work.....i presume thus it's a feature of sql2000.

yeh, its a sql2000 thing, but the only thing I really need to know is how to use multiple unions using cursors. It's still the problem of using Union within a loop... thanks anyway

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-15 : 08:31:47

quote:
Thanks for that, but I can't really post the whole table structure and xml doc because they are both huge... and besides you don't need it.

Believe it or not, I'm not a mind reader. You're suggesting that I should be able to drive a car with my eyes closed! Even if you're not giving me spoken directions! Think about it from our perspective: If you won't post the code there's not much chance for anyone to help you.

You can post 2 gigs worth of text on SQL Team, I'm pretty sure that's enough to cover some CREATE TABLE statements and the desired output.

quote:
The only thing I want to know is how to create a union operator to do an FOR XML EXPLICIT stored proc using cursors.

I CAN say with authority that this is going to cause you a great deal of frustration. I think that if this was a viable way to do it you'd have it figured out by now.

quote:
I'm using about twenty other FOR XML EXPLICIT stored procs in the DB and they all work fine, and I understand the explicit syntax... just not sure how I can make n amount of unions using a cursor without writing dynamic SQL

And since I can't see the code I can't guess where the problem might be, but it doesn't sound promising.

andymaule
Starting Member

6 Posts

Posted - 2002-07-16 : 10:28:07

sorry robvolk,

I'm not meaning to wind you up or anything, its just the answer to my question really doesn't require you to need any of my table data or anything. I've probably explained the problem badly in the first place. I'll try again...

I want to union the following select statements
<pre>
select 'First row' as columnName
select 1 as columnName
select 2 as columnName
select 3 as columnName
select 'Last row' as columnName
</pre>

The problem is I want to use a while loop within the query and still want to be able to union the results produced in this loop.
<pre>
DECLARE @COUNTER
SELECT @COUNTER = 1

SELECT 'First Row' as columnName

WHILE @COUNTER < 4
BEGIN
UNION
SELECT @Counter as columnName
END
UNION
SELECT 'Last Row' as Column Name

GO
</pre>

This procedure always has syntax errors when I try and union the statements outside the loop with the statements inside the loop. I can't even find a way to union just the statements inside the loop. I hope I've explained the problem a bit better this time. Sorry if I caused any confusion the first time.

I hope you can help me out.

Thanks

Andy

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-16 : 10:45:28

Have you considered creating a sequence table? It's just a regular table with a single column of integer values. You can use them for all kinds of neat things:

http://www.sqlteam.com/item.asp?ItemID=5857
http://www.sqlteam.com/item.asp?ItemID=2652

It really doesn't make sense to construct a loop when you can simply do:

SELECT * FROM Sequence WHERE ID Between 1 AND 100

BTW, you can't do:

select 'First row' as columnName
select 1 as columnName
select 2 as columnName
select 3 as columnName
select 'Last row' as columnName

Because you're mixing character data and integer data in the same column. You can do something like this:

select 'First row' as columnName
UNION
select Cast(ID as varchar) as columnName FROM Sequence WHERE ID Between 1 AND 3
UNION
select 'Last row' as columnName

All you need to do is modify the WHERE clause to return as many rows as you need from the sequence table.

HTH

andymaule
Starting Member

6 Posts

Posted - 2002-07-16 : 12:29:00

Thanks for that... that approach seems to work pretty well. I hadn't even considered that way of looking at it.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources