Error on executing rename statement

  • VSSGeorge

    SSCrazy Eights

    Points: 8144

    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: 22135

    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

  • Thom A

    SSC Guru

    Points: 98564

    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.

  • VSSGeorge

    SSCrazy Eights

    Points: 8144

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

    Also grateful to Thom for explaining more on this.

    • This reply was modified 1 month, 2 weeks ago by  VSSGeorge.

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

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