Global variable in stored procedure

Hi I'm having problems using a global variable that has been declared as a varchar in a stored procedure. The stored procedure is recursive, and after calling itself I'm no longer able to access the global variable, see the sp bellow.

Any help would be great

Thanks

Dan

CREATE PROCEDURE dbo.kb_c_getChildren
(
@.CategoryID uniqueidentifier
)
AS
SET NOCOUNT ON

/* Global variables */
if @.@.nestlevel = 1
begin
declare @.@.CatList varchar (8000)
set @.@.CatList = ''
end

/* Find children */

declare @.child uniqueidentifier
declare children cursor local for
select CatID from Category where Parent_CatID = @.CategoryID

open children

fetch next from children
into @.child

while @.@.fetch_status = 0
begin
set @.@.CatList = @.@.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child)
begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child
end

fetch next from children
into @.child

end

close children
deallocate children

print @.@.CatList

RETURN 1When I looked in Books On Line for Global variables I didn't find anyhting to support what you are trying to do. Would this work?

CREATE PROCEDURE dbo.kb_c_getChildren(
@.CategoryID uniqueidentifier,
@.CatList varchar(8000) = Null OUTPUT)
AS
SET NOCOUNT ON

/* Find children */

declare @.child uniqueidentifier
select @.child = min(CatID)
from Category
where PArent_CatID = @.CategoryID

while (@.child is not null) begin
set @.CatList = @.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child) begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child, @.CatList OUTPUT
end

select @.child = min(CatID)
from Category
where Parent_CatID = @.CategoryID
and CatID > @.child
end

if (@.@.nestlevel = 1)
print @.CatList

RETURN 1

I changed from using a cursor to a simple select and test, this is just a personnal thing for me, and changed your catlist to be an optional output parameter. On the 2nd and subsequent calls to the SP you will past your populated catlist to kb_c_getChildren, modify the contents, and return it to the calling sp. on the last itiration you should fall out of the while loop print the results.|||Hi Paul,

Thanks for the info, couldn't use the select statement as I'm using uniqueidentifers, but the output parameter works a treat.

Thanks again

Dan