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
