Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

Using BEGIN and END in SQL SERVER

Hi Experts,
What is the difference between using BEGIN TRANSACTION and END TRANSACTION, and using BEGIN and END?
When do we use one over the other?

Thanks in advance,
mrotor
0
mainrotor
Asked:
mainrotor
3 Solutions
 
Lee SavidgeCommented:
Begin and End delimit a block of SQL statements, for example:

if @myval = 1
begin
 update table...
 insert into ...
end

Open in new window


See here for more: http://msdn.microsoft.com/en-gb/library/ms190487%28v=sql.100%29.aspx


If you didn't have the begin and end there it would only do the first statement as part of the IF statement and the insert would always happen.

Begin transaction allow you to wrap up a series of statements that are held in a transaction so that you can test for error conditions and if one is found you can rollback a transaction. If not you can commit it and it will physically make the changes in the data.

There isn't an "end transaction" because you either commit or rollback

http://msdn.microsoft.com/en-gb/library/ms188929%28v=sql.100%29.aspx
0
 
Priya SudharsanCommented:
You need transations when you want to do more than one things, either all of them or none.

Begin and End are used to group the statements as logical. They may used in IF WHILE and CASE statements.

You can simply use BEGIN and END without any of the above statements.

Its just for a readability. They just group the statements and you would be able to see the steps clearly. Thats all.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
BEGIN and END are used to define a block of SQL statements depending of the logical of your code (Condition block, Loop block, Stored Procedure block, ...).

BEGIN TRANSACTION and END TRANSACTION are used to define a block of operations (INSERT, DELETE, UPDATE, SELECT, ...) to act as one and being committed (if everything's ok) or rollback (if something goes wrongs) so you can maintain the integrity of your database.
0
 
mainrotorAuthor Commented:
I will try some of your suggestion and get back to you all. Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
mainrotor - How's it going?  Looks like you asked us a softball question that multiple experts have answered..
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now