CODEWITHSUNDEEP
sqlsql-serverssms
Dazed_
Dazed_

Reputation: 31

Using 'GO', transactions & semi-colons

In the code below, is the use of GO, transactions and semi-colons correct?

I appreciate these questions have been asked separately many times but I am struggling when using them in combination and would be thankful for any guidance.

I am unsure whether it is necessary to use transactions in this circumstance.

USE TestingDB
GO

DECLARE @CustomerContactID int = 278800

BEGIN TRANSACTION      

DELETE
FROM            dbo.CustomerContact
WHERE           CustomerContact_CustomerContactID = @CustomerContactID;


DELETE
FROM            dbo.CustomerContactComs
WHERE           CustomerContactComs_CustomerContactID = CustomerContactID;

DELETE
FROM            dbo.CustomerContactAddress
WHERE           CustomerContactAddress_CustomerContactID = @CustomerContactID;

COMMIT TRANSACTION;

Upvotes: 3

Views: 101

Answers (1)

René Vogt
René Vogt

Reputation: 43936

  1. The semicolons are obsolete not necessary in T-SQL (except for Common Table Expressions and Service Broker statements if those are not the first statements in a batch). So it's a matter of taste if you want to use them. But Microsoft recommends to always use them. (See the first two comments below)

  2. The order of your DELETE statements seems wrong. You might at first want to delete the detail data from CustomerContactComs and CustomerContactAddress and then the CustomerContact

  3. The transaction might be necessary if you want to avoid situations where you only delete a part of the information, e.g. only CustomerContactComs but not the rest.

  4. That leads directly to the GO. You should not insert any GO statements between the statements in the transaction. GO is not part of T-SQL but is used for tools such as Management Studio to indicate separate batches.
    So if there is a GO the previous statements are send to the server as one batch. If one statement in that batch raises an error, the remaining statements of that batch will not be executed.
    But if you insert a GO here, the following statements would be a new batch and so the transaction could be committed although a previous statement failed, which is probably not what you want.

Upvotes: 3

Related Questions