Best practices for committing a transaction in SQL Server where TRY CATCH is usedWhy is this rollback needed...
How to deal with loss of decision making power over a change?
Installing PowerShell on 32-bit Kali OS fails
Perfect riffle shuffles
For airliners, what prevents wing strikes on landing in bad weather?
Why are all the doors on Ferenginar (the Ferengi home world) far shorter than the average Ferengi?
Can I create an upright 7ft x 5ft wall with Minor Illusion?
A known event to a history junkie
What would you call a finite collection of unordered objects that are not necessarily distinct?
I'm in charge of equipment buying but no one's ever happy with what I choose. How to fix this?
word describing multiple paths to the same abstract outcome
How can I successfully establish a nationwide combat training program for a large country?
Resetting two CD4017 counters simultaneously, only one resets
How do I repair my stair bannister?
Is there an wasy way to program in Tikz something like the one in the image?
What should I use for Mishna study?
Would it be legal for a US State to ban exports of a natural resource?
Fast sudoku solver
The One-Electron Universe postulate is true - what simple change can I make to change the whole universe?
How did Monica know how to operate Carol's "designer"?
Identify a stage play about a VR experience in which participants are encouraged to simulate performing horrific activities
How to prevent YouTube from showing already watched videos?
Is there a problem with hiding "forgot password" until it's needed?
Meta programming: Declare a new struct on the fly
Freedom of speech and where it applies
Best practices for committing a transaction in SQL Server where TRY CATCH is used
Why is this rollback needed when using sp_addextendedproperty in a stored procedure?Handling exceptions in stored procedures called using insert-exec blocksSleeping SPID blocking other transactionsconnection pooling, transactions, nested transaction and rollbackSQL Server: affect other transactions?How to retain good records when inserting from stored procedure and still catch the error message?relationship between transaction chaining and autocommitHow to handle errors in a transaction in a stored procedure?Investigating errors from strange queryHandling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements'
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they subjective choices?
**Option A**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
**Option B**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
IF @@trancount > 0 COMMIT TRANSACTION
In Option B is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?
sql-server transaction exception
asked 2 mins ago
user20358user20358
126114
add a comment |
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they subjective choices?
**Option A**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
**Option B**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
IF @@trancount > 0 COMMIT TRANSACTION
In Option B is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?
sql-server transaction exception
asked 2 mins ago
user20358user20358
126114
add a comment |
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they subjective choices?
**Option A**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
**Option B**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
IF @@trancount > 0 COMMIT TRANSACTION
In Option B is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?
sql-server transaction exception
asked 2 mins ago
user20358user20358
126114
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they subjective choices?
**Option A**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
**Option B**
CREATE PROCEDURE DummyProc
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
IF @@trancount > 0 COMMIT TRANSACTION
In Option B is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?
sql-server transaction exception
sql-server transaction exception
asked 2 mins ago
user20358user20358
126114
asked 2 mins ago
user20358user20358
126114
asked 2 mins ago
user20358user20358
126114
asked 2 mins ago
user20358user20358
126114
asked 2 mins ago
user20358user20358
126114
126114
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233079%2fbest-practices-for-committing-a-transaction-in-sql-server-where-try-catch-is-use%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233079%2fbest-practices-for-committing-a-transaction-in-sql-server-where-try-catch-is-use%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
