Using Cursors in Recursive calls
-
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
-
-
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
-
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