How to navigate recursive stored procedures results

[Guest]

Hi all, I've written a recursive stored procedure that will navigate one of my tables in a hierarchical fashion.

When I run the stored procedure within query analyzer it returns the proper data.

However, when I "fill" the DataSet with them - there only appears to be one row of data.

I should also add that instead of returning the data using a "select <column(s) from <table_name>" I'm using a cursor data type and am returning the data using this syntax:
select <local_variable1> as <name of first column in table>, <local_variable2> as <name of second column in table>

The proper data must be filling the dataset (since the stored procedure returns the right data when I execute it from within query analyzer). But I'm just not certain of how to get at it.

Thanks,
Novice

 

[William \(Bill\) Vaughn]

I expect that the Fill is creating one DataTable for each resultset that
contains a rowset. How many DataTable objects were created under the
DataSet?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi all, I've written a recursive stored procedure that will navigate one

of my tables in a hierarchical fashion.

When I run the stored procedure within query analyzer it returns the proper data.

However, when I "fill" the DataSet with them - there only appears to be one row of data.

I should also add that instead of returning the data using a "select

select <local_variable1> as <name of first column in table>,

The proper data must be filling the dataset (since the stored procedure

returns the right data when I execute it from within query analyzer). But
I'm just not certain of how to get at it.

 

[Guest]

I just checked the DataSet.Tables.Count property and to my surprise there are 54 tables returned (exactly the number of records that should be returned from the result set) is there a way to merge all of them together (I'm using a strongly typed dataset and I would like to put them into the datatable I've created for that database table)? Ever since I made that post I've been reading up on hierarchical data and recursive stored procedures and a couple of other questions have occurred to me.

I've found many people (some who have VERY strong SQL skills) who are against using recursive stored procedures and who instead advocate the use of temporary tables to hold what amounts to the stack that would occur in a recursive stored procedure or all of the traversed data and then simply returning all data in the temporary table.

I'm using SQL Server 2000 - is there any reason I should worry about using a recursive stored procedure and opt for using a temp. table? Also, do I need to worry about an application that could potentially have over a thousand concurrent users running into synchronization or data independence problems with temporary tables - i.e. a user unable to create their temporary table or worse having one user referencing another user's temporary table. The server machine has more than enough memory to handle 1000*(total memory required for single instance of application).

In summary, my questions are:
1. Is there some way of merging all the separate tables in my strongly typed dataset into one table, so I can iterate over it as usual - i.e.
for each dataRow in dataSet.Tables(0)
or
for each dataRow in dataSet.table_name

2. Are there dangers/inefficiencies associated with using recursion instead of temporary tables in SQL Server 2000?

3. Is there any chance that the temporary tables being used by an ASP.NET application can become corrupt when the number of users becomes very large?

Thanks,
Novice

 

[William \(Bill\) Vaughn]

Sure. Go ahead and SELECT INTO a #Temp in the SP and in the final step, do a
SELECT * FROM #Temp to return a single rowset. (Yes, this is a proper use of
SELECT *). You can also do a UNION query to do this as well.

I'm not at all clear by what you're trying to accomplish, but remember that
#temp tables created by a SP do not exist after the SP is done. How you
handle server-side state is really important to scalability and basic
functionality. Consider that many approaches that work fine in "connected"
architectures fall apart in Web/disconnected approaches.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I just checked the DataSet.Tables.Count property and to my surprise there

are 54 tables returned (exactly the number of records that should be
returned from the result set) is there a way to merge all of them together
(I'm using a strongly typed dataset and I would like to put them into the
datatable I've created for that database table)? Ever since I made that
post I've been reading up on hierarchical data and recursive stored
procedures and a couple of other questions have occurred to me.

I've found many people (some who have VERY strong SQL skills) who are

against using recursive stored procedures and who instead advocate the use
of temporary tables to hold what amounts to the stack that would occur in a
recursive stored procedure or all of the traversed data and then simply
returning all data in the temporary table.

I'm using SQL Server 2000 - is there any reason I should worry about using

a recursive stored procedure and opt for using a temp. table? Also, do I
need to worry about an application that could potentially have over a
thousand concurrent users running into synchronization or data independence
problems with temporary tables - i.e. a user unable to create their
temporary table or worse having one user referencing another user's
temporary table. The server machine has more than enough memory to handle
1000*(total memory required for single instance of application).

In summary, my questions are:
1. Is there some way of merging all the separate tables in my strongly

typed dataset into one table, so I can iterate over it as usual - i.e.

for each dataRow in dataSet.Tables(0)
or
for each dataRow in dataSet.table_name

2. Are there dangers/inefficiencies associated with using recursion

instead of temporary tables in SQL Server 2000?

3. Is there any chance that the temporary tables being used by an ASP.NET

application can become corrupt when the number of users becomes very large?

 

[Guest]

In my web architecture, the identity is not "flow"ing to the SQL Server - that is - I'm using a single SQL account to access the SQL server for all the different users of the system.

Does that mean that there could be problems with keep multiple #temp tables separate when multiple users are causing multiple instances of the server app. to invoke the same stored procedure?

Also, I tried doing a union using my recursive procedure, but I can't union a SQL query and the results of a stored procedure.
Here is a simplified version of my stored procedure:
CREATE PROC recursive_proc1 @someUID int
AS
DECLARE @some_UID int, @parent_UID int, @ordering_attribute int
DECLARE cur_Level CURSOR LOCAL FOR
SELECT * from some_table
where some_table.parent_UID = @someUID
order by some_table.ordering_attribute
OPEN cur_Level
FETCH NEXT FROM cur_Level INTO @some_UID, @parent_UID, @ordering_attribute
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @some_UID AS UID, @parent_UID AS parent_UID
--alternatively I could use the query below to use a regular select query - but I
--still can't union it with the results of the "exec recursive_proc1"
-- SELECT * from some_table where UID = @some_UID
EXEC recursive_proc1 @some_UID
FETCH NEXT FROM cur_Level INTO @some_UID, @parent_UID, @ordering_attribute
END


CLOSE cur_Level
DEALLOCATE cur_Level

GO

Thanks,
Novice

 

[William \(Bill\) Vaughn]

#Temp tables belong to the connection. In a web architecture, while the
connection is inherited by the next user, its contents are flushed
beforehand so any residual #Temp tables would be dropped. Each instance of
your application gets its own connection each time it executes. When you
close the connection (and you should), the connection is returned to the
pool where it is inherited by another instance of your application when it
does an Open. At that point (and not before), the connection is reset.
No, I don't expect that you could UNION a SP, but remember that a SP can
contain several independent SELECT statements that can be combined with
UNION--assuming their schema matches.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

In my web architecture, the identity is not "flow"ing to the SQL Server -

that is - I'm using a single SQL account to access the SQL server for all
the different users of the system.

Does that mean that there could be problems with keep multiple #temp

tables separate when multiple users are causing multiple instances of the
server app. to invoke the same stored procedure?

Also, I tried doing a union using my recursive procedure, but I can't

union a SQL query and the results of a stored procedure.