Creating a check constraint on a renamed column

Hi

I am having a check constraint on a table column Col1.
Now i am renaming the table column from Col1 to Col2. I dropped the check constraint before renaming the column . Then I renamed the column and then i am recreating the check constraint on Col2. I have written all these commands in a single script. Now when i am trying to run this script it gives me error saying
"Invalid column name 'Col2'"
How can i resolve this issue since i need to run these commands in a single file only.

Commands:-

CREATE TABLE TRY(
ID INTEGER,
COL1 TINYINT,
CONSTRAINT CHK_COL CHECK (COL1 IN(1,2))
)

ALTER TABLE TRY DROP CONSTRAINT CHK_COL
EXEC SP_RENAME 'TRY.COL1', 'COL2'
ALTER TABLE TRY ADD CONSTRAINT CHK_COL CHECK(COL2 IN (1,2))

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server.

use the following script...

Code Snippet

CREATE TABLE TRY(

ID INTEGER,

COL1 TINYINT,

CONSTRAINT CHK_COL CHECK (COL1 IN(1,2))

)

Go

ALTER TABLE TRY DROP CONSTRAINT CHK_COL

Go

EXEC SP_RENAME 'TRY.COL1', 'COL2'

Go

ALTER TABLE TRY ADD CONSTRAINT CHK_COL CHECK(COL2 IN (1,2))

|||

As Mani indicated, SQL Server operates on a 'batch' of statements at a time. Without a GO between your two statements, the first statement has not completed, and the second statement is attempting to change a table that doesn't yet exists.

Adding the GO between the statements is required after CREATE object type operations if you wish to then work with those objects.

|||ok
now i need to give the above code in a BEGIN END block
can i execute a BEGIN END block within a BEGIN END block with a GO statement
|||

No.. You should not allowed to enclose GO with in BEGIN .. END.

It is logically incorrect.

Why you need to have in BEGIN .. END?