title: sp_rename in stored procedure does not work
body: <p>The 3 following statements work fine when executed consecutively but when I wrapped them in one stored procedure I am getting this error msg:</p>
  
  <blockquote>
    <p>Msg 207, Level 16, State 1, Line 10<br>
    Invalid column name 'split'.<br>
    Msg 207, Level 16, State 1, Line 9<br>
    Invalid column name 'split'.  </p>
  </blockquote>
  
  <p>The procedure definition:</p>
  
  <pre><code>CREATE procedure [dbo].[dsplit_SITEL_update]
  as
  begin
     set nocount on;
  
     IF EXISTS(SELECT * FROM sys.columns 
               WHERE [name] = N'split_' AND [object_id] = OBJECT_ID(N'dsplit_SITEL$'))
  
         exec sp_rename @objname = N'dsplit_SITEL$.split_', 
                        @newname = 'split', 
                        @objtype = 'COLUMN';
  
     insert into dsplit_SITEL
        select 
            CONVERT(varchar(10), b.row_date, 120) + '_' + CONVERT(nvarchar(10), b.split), 
            b.*
        from dsplit_SITEL a 
        right join dsplit_SITEL$ b ON a.pk = CONVERT(varchar(10), b.row_date, 120) + '_' + CONVERT(nvarchar(10), b.split)
        where a.pk is null and b.row_date is not null;
  
      if OBJECT_ID('dsplit_SITEL$', 'U') is not null
         drop table dsplit_SITEL$;
  END
  </code></pre>
  
  <p>What is going on here and how can I have the procedure work?</p>