This is a SqlServer 2000 question.
In my Transact SQL-code (actually a procedure called proc2) I'm calling a
procedure called proc1.
proc1 is returning a resultset, typically 1-3 rows. There is only one column
in the resultset. In my T-SQL code in proc2 I want to loop through the
resultset returned from proc1.

I thought that cursors could be used for this, but to me it seems that only
"plain" SELECT-statemenst are allowed as the cursor command, not calling
another procedure. Am I correct? If not, please let me know the syntax.

A workround I tried was to call the procedure (proc1) and temporary store
the resultset in a temporary  table, Then use a cursor to read from (loop
over) this table. But since my proc1 creates it's returned result using FOR
XML, the "Insert into #MyTempTable Exec proc1" is not allowed.

Any ideas how I can get around this? My main purpose is to concatinate the
rows returned from proc1 into one single varchar variable before returning
from proc2.

In advance: Thanks for your help!
Regards,
Ronald

Ronald Kloverod wrote:
Show quoteHide quoteWhat's the point of returning XML from the proc if you then want to do
further processing? If you want to do processing in TSQL you have to
use the language that TSQL speaks. In SQL Server 2000 that means tables
not XML docs.

Explain your goal and I expect there is a better solution.

Well, there is more than one reason, to explain them all may be too much I'm
afraid. But here's one of them:
proc1 is not my procedure. This is in a quite huge system, where many users
and programs are involved, and other databases as well. Proc1 belongs to
another database, and works well for many purposes. It cannot be changed,
since its used by many others. But in my case, I also need to do a bit more
with it.

And why do I want to do more with it? Well, my procedure proc2, should
return a recordset to a .Net client. The recordset looks like something like
this:
Column1: Customer_key
Column2: some-other-info
Column2: some-other-info
....
Column9: The Xml data.

Column9 is the only information coming from proc1, the rest is information
found or calcuated in my procedure proc2.

Then I have two challenges that must be solved:
1) The XML returned from proc1 must be "picked up" in some way, so it can be
included as a part of the resultset shown above.
2) If the XML returned from proc1 is splitted up in more than one row
("..For Xml..." does so if the string is long), I need to concatenate into
one single string before returning from proc2.

Hope this explains a bit more.

Still - independant of XML or not - I woner if TSQL can read through a
resultset returned from another procedure....

Regards,
Ronald


Show quoteHide quote

And why can't you just return two result sets from the proc2 procedure? As I
see it you're returning a set and an XML.


ML

---
http://milambda.blogspot.com/

Yes, that could be one possible option. I've thought about it, but if so,
some other problems appear.
Here's an example:
Result set from proc 2
row-1: Customer_key (Col1), somethinG (Col2),...,something(Col8)
row-2: Customer_key (Col1), somethinG (Col2),...,something(Col8)
row-3: Customer_key (Col1), somethinG (Col2),...,something(Col8)
row-4: Customer_key (Col1), somethinG (Col2),...,something(Col8)

Resultset from proc 1 (delivered as secoond rowset from proc1):
row-1: (part of?) xml for customer_key xx
row-2: (part of?) xml for customer_key xx
row-3: (part of?) xml for customer_key xx
row-4: (part of?) xml for customer_key xx
....
row-n: (last part of last xml) for customer xx

My problem is then to find out which, and how many, of the rows in the last
resultset, that belongs to each of the rows in the first resultset.

Ronald



Show quoteHide quote

So, you also need to chop the XML up in proc2? If you cannot identify
relationships between the XML nodes and the rows in your result set in .Net,
how are you planning on doing it in T-SQL?

In SQL 2005 you have full control over XML data, in SQL 2000, sadly, you
don't.


ML

---
http://milambda.blogspot.com/

Proc1 (returning 0-n rows with xml-data) will be called for each of the
customers which are represented in the first rowset. The idea was then to
loop over the resultset each time, and make a complete XML for each
customer - and then let it be the column #9 of the first (and only)
resultset.

But I still miss an answer to my original question (see subject field).
Isn't there any way too loop over the reslultset coming from a stored
procedure - just like I can do if the resultset comes as a result of an
SQL-statement (ref. cursors)?

Ronald


Show quoteHide quote

Have you tried Erland's example - the one David posted?

You'd have to execute proc1 for each customer and insert the result into a
temporary table, then you can join the two sets in a single result set that
proc2 can return to the application.


ML

---
http://milambda.blogspot.com/

Ronald Kloverod wrote:

Take a look at:
http://www.sqlxml.org/faqs.aspx?faq=104

TSQL can *process* a result set returned from a proc. You would usually
have to insert that result to a table first - which isn't a problem
unless you try to do stuff with XML. Processing a resultset doesn't
mean row-by-row processing of course. Usually we write set-based code
to process data in SQL.

Well, but as you can see of my original posting, this is exactly my
problem - the data to loop over ARE xml in my case.

Ronald.

Ronald Kloverod wrote:
Did you try the solution in the link I posted? Let us know if you need
more help I'm not sure if you still have a question.

Count Occurances in a column
Who has the highest bid?
Need Help Please...
How to compare strings for equality in Transact SQL
Updating Table with data from another table