Re: How to navigate recursive stored procedures results
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 06/30/04
- Next message: Jim Moon: "SQL Server "CURRENT_USER" AND "user_name()""
- Previous message: .Net Newbie: "Help! SQLTransaction and Identity(?) column in SQL Server 2K"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 30 Jun 2004 10:28:35 -0700
#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. __________________________________ "Novice" <6tc1ATqlinkDOTqueensuDOTca> wrote in message news:00CAA3A1-E5D1-47F2-8F3E-F5B2A6B1DB31@microsoft.com... > 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" wrote: > > > 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. > > __________________________________ > > > > "Novice" <6tc1ATqlinkDOTqueensuDOTca> wrote in message > > news:63AE6D7D-33EF-4EFE-9824-88AFC04EB56E@microsoft.com... > > > 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" wrote: > > > > > > > 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. > > > > __________________________________ > > > > > > > > "Novice" <6tc1ATqlinkDOTqueensuDOTca> wrote in message > > > > news:307B409B-F248-4C7D-A6CF-72AE10C67514@microsoft.com... > > > > > 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 > > > > > > > > > > > > > > > > > >
- Next message: Jim Moon: "SQL Server "CURRENT_USER" AND "user_name()""
- Previous message: .Net Newbie: "Help! SQLTransaction and Identity(?) column in SQL Server 2K"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
