May 1, 2003 at 12:30 pm
I'm working on a recursive stored procedure to create an in-order traversal of a hierarchy stored in a single table (key and parent_key).
I am aware of the debate about whether or not to use cursors. For this stored proc, I was not concerned about speed, so I used a cursor. It looks like this stored proc might do the job, except that the first recursive call attempts to redeclare the already existing cursor.
Is there a way to declare a cursor using a variable as the name of the cursor? If so, I can't find it.
Thanks in advance,
Dan
May 1, 2003 at 5:21 pm
You could try to write wrapped in Dynamic SQL. Otherwise it goes out of scope and cannot be done at all.
May 1, 2003 at 5:26 pm
No, your cursor name can not be variable.
If you are going to declare a cursor with the same name, you have to first deallocate it.
At the end of the cursor loop, close the cursor and then deallocate it.
May 2, 2003 at 3:09 am
quote:
I'm working on a recursive stored procedure to create an in-order traversal of a hierarchy stored in a single table (key and parent_key).Is there a way to declare a cursor using a variable as the name of the cursor? If so, I can't find it.
Don't think it can be done, however I think you are looking for the wrong solution.
If you declare the cursor as LOCAL then each instance of the sp has its own cursor which does not interfere with others
HTH
Pete
Pete
Pete
May 2, 2003 at 8:46 am
Thanks for the input, all.
I considered the dynamic SQL approach suggested by Antares686, which I read about yesterday when I was looking for a solution in the voluminous set of posts on this site. I have not written a dynamic stored proc before, and if time was no issue, I might explore this route.
The "close and deallocate" method suggested by Rawhide would not help me because I was looking for a way to keep cursors open as the stored proc was called recursively. I asked the question because I hoped to rename the cursors by recursion level, such as cc_cursor0, cc_cursor1, cc_cursor2, etc.
The suggestion by Pete@jcd accomplished what I needed. The LOCAL keyword enabled the stored procedure to recognized each cursor locally and individually at each level of recursion.
The code is provided below. I realize there may be more efficient ways to do this (function instead of stored proc, ways to avoid cursors, different use of temporary tables, etc.), but this worked for me. To run the procedure, the following execute statement was used:
exec omsp_traverse_cc_tree 1, 0
where 1 was the cost_center_key for the top of the branch of the tree I was interested in, and 0 was the current level of recursion.
The table that this code operates on is defined as follows:
CREATE TABLE [dbo].[CostCenter] (
[cost_center_key] [int] NOT NULL ,
[cost_center_code] [varchar] (10) NULL ,
[cost_center_name] [varchar] (75) NULL ,
[cost_center_parent_key] [int] NULL
)
CREATE procedure omsp_traverse_cc_tree @parent_key int, @curlevel smallint AS
declare @row_id smallint
declare @cc_key int
declare @cc_code varchar(10)
declare @cc_name varchar(75)
declare @newlevel smallint
declare @outstr varchar (100)
declare @print_level smallint
if @curlevel = 0
begin
create table #traversal
(print_levelsmallint,
row_idsmallint,
cc_keyint,
cc_codevarchar(10),
cc_namevarchar(75))
end
SELECT @row_id = count(*) FROM #traversal
SET @row_id = @row_id + 1
select @cc_key = cost_center_key, @cc_code = cost_center_code, @cc_name = cost_center_name
FROM CostCenter
WHERE cost_center_key = @parent_key
if @curlevel = 0
begin
INSERT INTO #traversal
VALUES (@curlevel, @row_id, @cc_key, @cc_code, @cc_name)
end
-- create a cursor to select cost centers that have parameter cost center as parent
declare cc_cursor cursor local for select cost_center_key, cost_center_code, cost_center_name
FROM CostCenter where cost_center_parent_key = @parent_key order by cost_center_key
open cc_cursor
fetch next from cc_cursor into @cc_key, @cc_code, @cc_name
SELECT @row_id = count(*) FROM #traversal
set @row_id = @row_id + 1
set @newlevel = @curlevel + 1
-- while loop runs as long as there are rows in the cursor to process
while @@FETCH_STATUS = 0
begin
-- for each row in the cursor
INSERT INTO #traversal
VALUES (@curlevel, @row_id, @cc_key, @cc_code, @cc_name)
exec omsp_traverse_cc_tree @cc_key, @newlevel
fetch next from cc_cursor into @cc_key, @cc_code, @cc_name
SELECT @row_id = count(*) FROM #traversal
set @row_id = @row_id + 1
end
-- Close and deallocate the cursor.
close cc_cursor
deallocate cc_cursor
if @curlevel = 0
begin
declare trav_cursor cursor for select print_level, row_id, cc_key, cc_code, cc_name FROM #traversal
ORDER BY row_id
open trav_cursor
fetch next from trav_cursor into @print_level, @row_id, @cc_key, @cc_code, @cc_name
while @@FETCH_STATUS = 0
begin
set @outstr = cast(@print_level AS varchar(2)) + ' - ' + cast(@row_id AS varchar(3)) + ' - '+ cast(@cc_key AS varchar(3)) + ' - ' + @cc_code + ' - ' + @cc_name
print @outstr
fetch next from trav_cursor into @print_level, @row_id, @cc_key, @cc_code, @cc_name
end
end
May 2, 2003 at 11:50 am
Oops, please note, with above script, forgot to close and deallocate trav_cursor. Sorry.
Dan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply