Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-08 : 08:07:37
|
| john writes "What is the relationship between stored procedures and transactions?Is a stored procedure a transaction? Can it be? Do I have to nest the whole procedure in a BEGIN...END block?Should I use the BEGIN TRAN/COMMIT logic within a stored procedure to make it a transaction?Should I do that for each transact statement within the stored procedure or make the whole procedure one big BEGIN TRAN block ?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-08 : 08:12:55
|
quote:
What is the relationship between stored procedures and transactions?
None. You can have stored procedures without using transactions, and transactions without stored procedures.quote:
Is a stored procedure a transaction? Can it be? Do I have to nest the whole procedure in a BEGIN...END block?
No to all 3. A stored procedure is just a block of code that is named and stored in the database, so that it can be executed by name later on. It can have transactions in it, but doesn't have to. You do not have to have a BEGIN...END block, which is not a transaction anyway, it just labels a block of code as one unit.quote:
Should I use the BEGIN TRAN/COMMIT logic within a stored procedure to make it a transaction?
That depends on what you're trying to do. If you have several steps that all need to complete, or fail, as a unit, then yes, a transaction would be appropriate.quote:
Should I do that for each transact statement within the stored procedure or make the whole procedure one big BEGIN TRAN block?
Building a transaction for each statement is redundant, because each statement is atomic on it's own. You'd only declare a transaction for multiple statements that must all complete in order for the transaction to commit.Books Online describes transactions in great detail, if you haven't looked there yet you should, most or all of your questions will be answered better than I have. |
 |
|
|
|
|
|
|
|
the relationship of stored procedures and transactions