Is it possible pass to procedure open cursor from other procedure ??

Is it possible pass to procedure open cursor from other procedure ?
Any suggestions will be appreciated
Message posted via http://www.webservertalk.comYes, you can declare a cursor as an output a parameter of a stored
procedure. But it is most likely not the most efficient way to share data
between procedures. The alternatives are discussed by SQL Server MVP Erland
Sommarskog in the following article:
http://www.sommarskog.se/share_data.html
Jacco Schalkwijk
SQL Server MVP
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:bd838d17bc034f26a19b475812a8eeb7@.SQ
webservertalk.com...
> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com|||Yes, but it's probably not a good idea. Could you explain your
requirement. There's sure to be a better way.
David Portas
SQL Server MVP
--|||In my db i call to some procedure suppese MyProc from application and pass
some XML,
inside the MyProc i parse the xml, insert the data to temporary table and
now according to some information that i got from xml call to other
procedure suppose proc1 or
proc2 or ... and in one of these procedures i perform some operation on
data that exist in temporary table. Actually i solve this problem with
temporary table (#myTable) however it's not good idea to use it and i can't
to use it, i don't know what to do.
The problem is that according to data in XML i call to appropriate
procedure, i can parse the xml in Myproc sp and call to relevant nested
procedure
proc1 or proc2, ... and pass this XML again however i don't want to open
and
parse the XML twice
Message posted via http://www.webservertalk.com|||This doesn't make a lot of sense to me as a design for a process in SQL
Server. You should parse your XML once only in order to load it into
appropriate tables. TSQL provides a proc to do this:
sp_xml_removedocument. Then execute procs from the data in tables. That
way you should avoid lots of messy cursors, temp tables and procedural
code. XML is for data-interchange only - it's a lousy way to persist
data and move it around inside the database.
David Portas
SQL Server MVP
--|||CORRECTION: sp_xml_preparedocument is the name of the proc you want.
David Portas
SQL Server MVP
--|||my problem is that after i parse the xml i fill temporary table with it's
data and now i need to call to other procedure to which i want pass
temporary table, but it isn't good idea also pass an open cursor is not
good idea
Message posted via http://www.webservertalk.com|||So why create two separate SPs and why load the data into a temporary
table or a cursor? You seem to be looking for a solution to a problem
that wouldn't exist if you made a better design.
David Portas
SQL Server MVP
--|||because according to the data that i got from XML i call to appropriate
procedure
Message posted via http://www.webservertalk.com|||OK. So load the XML data into tables where it belongs. Then execute the
logic for BOTH procedures but add or modify the WHERE clauses in your
DML code such that the logic only executes as appropriate. Example
pseudo-code:
Instead of this:
IF x=1
EXEC usp_proc1
IF x=2
EXEC usp_proc2
Do this:
..
WHERE x = 1
..
WHERE x = 2
In other words, adopt the declarative, set-based approach rather than a
procedural approach.
David Portas
SQL Server MVP
--