Usage of GO ( batch separator) in T-SQL code

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 247
I will explain the usage of GO statement with the help of below sample code..

--Sample Data
CREATE TABLE Dept
(
DeptID int,
DeptName varchar(100)
)
INSERT INTO Dept Values( 1, 'IT'), ( 2, 'HR')


Now, i wanted to add a new column LocationID to the existing table (DEPT). Then, wants to add one check constriant on newly added column.


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'CK_Dept_LocationID')
BEGIN
ALTER TABLE Dept ADD LocationID INT NULL
END
ELSE
print 'WARN : Column LocationID of DEPT table already exists'

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'CK_Dept_LocationID')
BEGIN
ALTER TABLE Dept ADD CONSTRAINT CK_Dept_LocationID CHECK (LocationID>100)

ALTER TABLE [dbo].[Dept] CHECK CONSTRAINT [CK_Dept_LocationID]
END
ELSE
PRINT 'WARN : Constraint CK_Dept_LocationID OF DEPT table already exists'


If you run the above code, it returns the below error

Msg 207, Level 16, State 1, Line 17
Invalid column name 'LocationID'.



The reason for above error is that the LocationID column is NOT available at the time of compilation. Here, the GO statement makes sense a lot.....

Simple solution for the above issue is,
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'CK_Dept_LocationID')
BEGIN
ALTER TABLE Dept ADD LocationID INT NULL
END
ELSE
print 'WARN : Column LocationID of DEPT table already exists'
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'CK_Dept_LocationID')
BEGIN
ALTER TABLE Dept ADD CONSTRAINT CK_Dept_LocationID CHECK (LocationID>100)

ALTER TABLE [dbo].[Dept] CHECK CONSTRAINT [CK_Dept_LocationID]
END
ELSE
PRINT 'WARN : Constraint CK_Dept_LocationID OF DEPT table already exists'


This code snippet runs fine without any issues......

NOTE: After making schema changes (e.g. adding a new column to an existing table), the statements using the new schema( means newly added columns) must be compiled separately in a different batch.. So we have to separate the T-SQL code with GO statement here.

Comments or Responses

Login to post response