Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    England
    Posts
    21

    Unanswered: 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 1

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    When 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?

    Code:
    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.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    England
    Posts
    21

    Talking

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •