February 27, 2002 at 4:32 pm
I'm fairly new to SQL programming, so this may be a trivial question, but how do I run a hierarchical recordset query such as the following from a sql server stored procedure? I just get syntax errors, although it runs fine in vbscript.
Thanks, David
SHAPE {Select * from staff} APPEND ({select * from staff_pub_link} RELATE staffid TO staffid) as rsPubs
February 27, 2002 at 4:34 pm
you'd do a join.
select *
from staff s
inner join
staff_pub_link sp
on s.staffid = sp.staffid
Steve Jones
February 27, 2002 at 6:31 pm
Not sure a join will return the same hierarchy as using the shape provider. Depending on the amount of data returned and the frequency it is queried, you could use a cursor to loop through the parent and child recordsets and generate the hierarchical output you desire to a table. Then query the table. Another approach may be to look at using for xml explicit (not sure if it will give you the flexibility you desire, but its worth a look see).
February 27, 2002 at 7:38 pm
Doing a join as you suggested Steve means that multiple copies of each "Staff" record are returned - wasting a lot of space/time, as I understand it (correct me if I'm wrong).
Thanks for the quick response though - and the XML suggestion jwiner.
February 27, 2002 at 8:33 pm
There is no way to run a shape command in TSQL. In order to use the shape language you need to go through ADO, that is why you need two providers (Provider=MSDataShape; Data Provider=SQLOLEDB.1)in your connection string. If you look at shaping all it really does is return multiple record sets that can be related by a foreign key. You could simulate this by running the individual queries and doing the relation yourself. ADO 2.5 and above will allow you to return multiple recordsets from a stored procedure. Using the adoRS.nextrecordset method you can get to the individual recordsets. However having said that, I would agree with John that returning the data via XML would give you a better representation of what you are after.
February 27, 2002 at 9:20 pm
You'd have to weigh the extra overhead of XML to get it back to the client vs the ease of working with it. If the shaped provider will work (and it has its quirks) why not just use ADO and do it? Stored procs arent the end all for solutions.
Andy
February 28, 2002 at 9:44 am
I agree there will be duplicate records. However as suggested above, this is a tradeoff. The join is a simple item and you can easily test for duplicate Staff records in a loop to get all the child records. however, this is additional overhead (larger result set, more work on the client). The ADO shape is designed to handle this for you, but it is (slightly) more complex to call.
If you want to do XML, it can be done, but it's complex and will take some work to understand how the XML structure is formed. That's time there.
It comes down to where you want to spend the time. And where you are more comfortable. I agree with Andy, stored procs are not always the answer. Though you could build two and call one from each query of the shape (I think).
Steve Jones
February 28, 2002 at 11:04 am
Leon and I did some experimenting with Shape a few months ago, found out that basically it returns ALL rows for the child records, then filters the display for you in the recordset. Handy, but depending on number of records a lot of overhead. You can reduce this by using "smart" sql for each tier of the shape that understands its relationship to its parent and therefore only brings back needed records. Not saying not to use shape, just to double check your performance when done.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply