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';
ENDI 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'.
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 18, 2020 at 12:59 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply