recordsets return by stored procedures?
Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201354 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
If they have the same set of column names you could do a union of the results creating a single recordset adding your own custom column to identify which set of results you are looking at: SELECT f_name, l_name, 'contact' as myGrouping FROM contacts UNION SELECT f_name, l_name, 'clients' as myGrouping FROM clients etc... But the bigger question is - why is your stored proc returning an unknown number of recordsets? This type of question usually can be answered by revisiting the stored proc to get it to generate a single recordset in the first place. If it just gets too complex a different answer would be to put the results into a temp table and return that recordset: SELECT f_name, l_name, 'contact' as myGrouping INTO #myTempTable FROM contacts INSERT INTO #myTempTable SELECT f_name, l_name, 'clients' as myGrouping FROM clients HTH, Adam Howitt On Apr 4, 2005 10:06 AM, Johnny Le [EMAIL PROTECTED] wrote: Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
And unknown amount..can't you just return empty sets if now records exist (unless you are using god awful dynamic SQL within the SP! ) -Original Message- From: Johnny Le [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 16:07 To: CF-Talk Subject: recordsets return by stored procedures? Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201359 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
Assuming that each recordset you're returning has something that could uniquely identify it (eg a productID), I would probably return just 1 recordset, with all the data that you were returning via multiple recordsets combined - just add the ID number. Then when you're outputting or processing, just use the group attribute of cfoutput, or use QoQ. HTH, Alex -Original Message- From: Johnny Le [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 16:07 To: CF-Talk Subject: recordsets return by stored procedures? Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201361 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
Hi, Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Johnny JDBC may privde some reporting of this information... I don't remember off the top of my head, but the onTap framework has a tag designed to execute stored procedures with named parameters (uses a structure, i.e. the form scope for instance). I know it's designed to allow multiple result sets, although unfortunately I don't remember offhand if the automation is driven by the code or by meta-data returned from the database. If you're interested in going that route, check out my JDBC article in the ColdFusion Developer's Journal. http://www.sys-con.com/story/?storyid=45569de=1 Otherwise, if you have control over the stored procedure, I'd recommend rewriting it so that all of the recordsets are returned (regardless of conditions) with any unneeded recordsets simply being empty (a select statement which returns records where the table's primary key is null is an easy way to achieve this). If you need to distinguish these results from results which would otherwise return an empty recordset, you can return an output parameter from the procedure with an integer indicating the number of relevant recordsets. hth s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201362 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
Hi, Here is my stored procedure: CREATE PROCEDURE dbo.sp_organizations @parent_id int AS DECLARE @Name varchar(500) DECLARE @Parent int DECLARE cur_Level CURSOR LOCAL FOR SELECT Organization_id AS ID, Organization_nm, Parent_organization_id FROM Organization WHERE Parent_organization_id = @parent_id ORDER BY Organization_nm OPEN cur_Level FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent WHILE @@FETCH_STATUS = 0 BEGIN SELECT @parent_id AS ID, @Name AS Name, @Parent AS Parent EXEC sp_organizations @parent_id FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent END CLOSE cur_Level DEALLOCATE cur_Level GO Basically I have one table called organization and it has three columns: id, name, and parent_id. The parent_id will tell which organization is the parent of that organization. It goes into unlimited levels of parent-child relationships. I want to get all the children organizations that belongs to a specified parent_id. I am using MS SQL Server 2000 and CFMX. If you could help me to rewrite my stored procedure so that it would return just one recordset, I would greatly appreciate it. My experience with stored procedure is limited. So I have no ideas how to put these recordsets into a temp table. Thanks. Johnny ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201376 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
You may want to check out Joe Celco's nested set model (no refs but google should find it) for a neat way to handle this type of hierarchical information storage. In the meantime you could keep the cursor but insert the resulting recordsets into a working table before returning the complete resultset. You would create the working table if @depth = 1 (set a local variable and pass it to subsequent calls and drop it after the last call) or permanently create it which would give you better performance and the ability to use indexes. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201383 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: recordsets return by stored procedures?
Oh hell, that's hideously simple... CREATE PROCEDURE dbo.sp_organizations @parent_id int AS DECLARE @Name varchar(500) DECLARE @Parent int DECLARE cur_Level CURSOR LOCAL FOR SELECT Organization_id AS ID, Organization_nm, Parent_organization_id FROM Organization WHERE Parent_organization_id = @parent_id ORDER BY Organization_nm CREATE TABLE #child (id int, name nvarchar(500), parent int) OPEN cur_Level FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #child (id, name, parent) VALUES (@parent_id, @name, @parent) EXEC sp_organizations @parent_id FETCH NEXT FROM cur_Level INTO @parent_id, @Name, @Parent END CLOSE cur_Level DEALLOCATE cur_Level SELECT * FROM #child DROP TABLE #child GO The # tells SQL Server to create a temp-table. As a matter of fact, I'd eliminate the cursor too -- insert the first record into the temp table manually, then use this loop to insert all the child organizations: WHILE EXISTS (select * from organization where Parent_organization_id in (select id from #child) and organization_id not in (select id from #child)) BEGIN INSERT INTO #child (id, name, parent) SELECT organization_id, organization_nm, Parent_organization_id FROM organization where Parent_organization_id in (select id from #child) and organization_id not in (select id from #child) END (really #child is a bad name for a table but then, so are id and name bad names for columns). hth s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201391 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Joe Celko and Nested Sets; was Re: recordsets return by stored procedures?
Adam Howitt wrote: You may want to check out Joe Celco's nested set model (no refs but google should find it) for a neat way to handle this type of hierarchical information storage. In the meantime you could keep the cursor but insert the resulting recordsets into a working table before returning the complete resultset. You would create the working table if @depth = 1 (set a local variable and pass it to subsequent calls and drop it after the last call) or permanently create it which would give you better performance and the ability to use indexes. I highly recommend his book, SQL for Smarties[1]. Here's a few links that should help: 1. http://www.intelligententerprise.com/001020/celko.jhtml 2. http://www.dbmsmag.com/9604d06.html and http://www.dbmsmag.com/9603d06.html 3. http://troels.arvin.dk/db/rdbms/links/ 4. http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html 5. http://dbazine.com/tropashko4.shtml 6. http://www.sitepoint.com/article/hierarchical-data-database He also wrote a whole book on trees and hierarchies[2]. K. [1] http://www.amazon.co.uk/exec/obidos/ASIN/1558605762/ [2] http://www.amazon.co.uk/exec/obidos/ASIN/1558609202/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201392 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: recordsets return by stored procedures?
Is there a way to find out how many recordsets return by a stored procedure? Or is there a way to merge all the recordsets in a stored procedure so that it would only return 1 recordset? I have a stored procedure that would return an unknown number of recordsets depending on how many records we have in the database, but each recordset will have the same number of columns and column names. I need to know the number of recorsets it will return so I can do a loop for the cfprocresult. Why not just write the stored procedure so that it returns what you want it to return? In this case, for example, your stored procedure could create a temp table, select all the records you want into that temp table, then select from the temp table to return a single recordset. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201393 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
