Error on executing rename statement

Question

Post reply

Error on executing rename statement

VSSGeorge

SSCrazy Eights

Points: 8151

February 18, 2020 at 9:21 am

Go to Answer

#3726672

Error on executing rename statement in a table. Not able to debug this. Please help.

IF COL_LENGTH('VisitsMaster', 'RMIDS') IS NOT NULL
BEGIN

sp_RENAME 'VisitsMaster.RMIDS' , 'TeamIDs', 'COLUMN'
GO

Print 'RMIDS column renamed to TeamIDs in VisitsMaster Table in RegTracInt';
END
ELSE
BEGIN
Print 'RMIDS column not exists on VisitsMaster Table in RegTracInt';
END

I am getting these error messages below:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'sp_RENAME'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'ELSE'.

Neil Burton

SSC-Insane

Points: 23045
More actions
February 18, 2020 at 10:19 am
Answer
#3726675
You need to run
```
EXEC sp_rename
```
and remove the ''GO'. That is a batch separator so your IF is technically in a different piece of code from your ELSE.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99137 More actions · Answer 1

You can call an SP without using EXEC, however, it must be the first thing you do in the batch. From SQL Server Utilities Statements - GO:

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

As sp_rename is the third statement in the batch, it must include the EXECUTE keyword.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

Thanks Neil for your valuable answer. You made my day.

Also grateful to Thom for explaining more on this.

This reply was modified 5 years, 1 month ago by VSSGeorge.

Error on executing rename statement

Cookies on SQLServerCentral