Post reply

Using Cursors in Recursive calls

  • February 27, 2006 at 11:49 pm

    #93652

    hi all,

    im using a cursor in a stored procedure n calling itself recursively.

    Set @SQL='Declare d cursor FOR

          Select oObjname from tmpGD'+ @Table_Name1 + '

       Open d '

          Exec (@SQL)

          Print @SQL

      Fetch Next From d Into @Dep_Table_Name

    it is working properly but in recursive calls it gives the error that d cursor already present.

    after that i tried

    Set @SQL='Declare d cursor LOCAL FOR

          Select oObjname from tmpGD'+ @Table_Name1 + '

       Open d '

          Exec (@SQL)

          Print @SQL

      Fetch Next From d Into @Dep_Table_Name

    but then it wouldnt take the fetch statement also it gives an error that d is not declared.

    is there a way by which i can call cursor recursively within a sql query variable

    plz......help

  • February 28, 2006 at 1:22 am

    #623602

    Take a step backwards.

    What is the business problem that you are trying to solve

    LinkedIn Profile

  • February 28, 2006 at 2:12 am

    #623613

    well im making a global delete in which i need to pass only the table name and the record details and the SP will delete all the related dependet tables recors too.

    for this i have made the sp but am stuck as i need to use one cursor recursively

    this recursive cursor hold all the child table names.so it holds the children of each table in each instance of the sp.i.e. multilevel dependency.

    but this is req as i need to get the respective values from the parent table.

    as the table name is passed through a variable i have to use the sql query variable.

    in the above code im declaring a cursor in the sql variable and executing it.

    it works fine for the first run but in recursive it doesnt.that is y i need to know how do i implement a cursor in a generic manner so that in every recursive call a new cursor is generated.

    if u want i can paste the entire sp.

    let me know.

    viral 

  • February 28, 2006 at 3:06 am

    #623619

    I can't see, why a cursor would be necessary. Simply traverse your dependencies and issue a non-cursor call as long as you receive results.

    'DELETE FROM ' + @tableX + ' WHERE ....

    How do you store your dependencies?

    I'd rather use DRI because in a transactional environment  you're not guaranteed that no one inserts new records, AFTER you have traversed the corresponding branch, but before you're finished with the parent (in case at least the application checks for integrity).

    -------------------------

    In case you really like cursors for some reason , close and deallocate them, before redefining them. 


    _/_/_/ paramind _/_/_/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply