Stored Procedures

[Kent Anderson]

We are currently switching to stored procedures for a lot of our database
activity. The question has come up about the transactional nature of the
stored procedures. I was wondering if stored procedures can have
transactions in them or if you must start the transaction in your code and
call the stored procedure from there to get the safety of a transaction?

Thanks
Kent Anderson
EZYield.com
407-629-0900
www.ezyield.com

========================================
This electronic message transmission contains information from the Company
that may be proprietary, confidential and/or privileged. The information is
intended only for the use of the individual(s) or entity named above. If
you are not the intended recipient, be aware that any disclosure, copying or
distribution or use of the contents of this information is prohibited. If
you have received this electronic transmission in error, please notify the
sender immediately by replying to the address listed in the "From:" field.

[Alvaro Herrera]

On Tue, Sep 21, 2004 at 07:41:31AM -0400, Kent Anderson wrote:

We are currently switching to stored procedures for a lot of our database
activity. The question has come up about the transactional nature of the
stored procedures. I was wondering if stored procedures can have
transactions in them or if you must start the transaction in your code and
call the stored procedure from there to get the safety of a transaction?

There's only one transaction (whether it's an explicit transaction block
or an implicit one), and the query that invokes the stored procedure is
already running inside it. So the stored procedure always has the
safety of it, and it can't get out (except by raising an error and
aborting the whole thing). The transaction can only be committed
_after_ the stored procedure has finished succesfully.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Jajaja! Solo hablaba en serio!
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Alvaro Herrera]

On Tue, Sep 21, 2004 at 11:11:33AM -0700, Chris Travers wrote:

Alvaro Herrera wrote:

There's only one transaction (whether it's an explicit transaction block
or an implicit one), and the query that invokes the stored procedure is
already running inside it. So the stored procedure always has the
safety of it, and it can't get out (except by raising an error and
aborting the whole thing). The transaction can only be committed
_after_ the stored procedure has finished succesfully.

I am assuming that save points would still work as advertised in stored
procedures....

Not at all. What you actually use is exception blocks.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster