sp_rename Procedure with a Variable Column Name

Question

I am trying to use the sp_rename stored procedure with a variable since I do not have control over the source (an Excel file that changes the column name to have a date).

I drop a table, and then import that table. I then want to change the column name of that table so I used a schema query to find out what the column currently is so that I can change it with sp_rename:

USE ESS
GO

DECLARE @TotalName NVARCHAR(255)
DECLARE @ColName NVARCHAR(255)
DECLARE @NewName NVARCHAR(255)

SELECT
    @ColName = column_name
FROM
    information_schema.columns
WHERE 
    TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'serverinv_unix_system_assignments_staging'
    AND ordinal_position = 1;

SET @TotalName = CONCAT(N'dbo.serverinv_unix_system_assignements_staging.' ,@ColName)
SET @NewName = ''''+ @TotalName+ ''','''+''+'server_name'+''','''+'COLUMN'';'+''

-- Select @NewName

EXEC sp_rename @NewName

It works if I explicitly type it out like below.

 USE ESS
 GO

 EXEC sp_rename 'dbo.serverinv_unix_system_assignments_staging.Hostname as of 8/26/2018','server_name','COLUMN';
   GO

The error I get on the first code set is

Msg 201, Level 16, State 4, Procedure sp_rename, Line 0 [Batch Start Line 2]
Procedure or function 'sp_rename' expects parameter '@newname', which was not supplied.

Also the result of the @NewName variable is below 'dbo.serverinv_unix_system_assignements_staging.Hostname as of 8/26/2018','server_name','COLUMN'; — Zeus, Sep 19 '18 at 19:12
Son of a Gun.....I had an extra "e" in my assign"e"ments that was causing the error....thanks guys — Zeus, Sep 19 '18 at 19:31

score 2 · Answer 1 · answered Sep 19 '18 at 19:22

Oh, I see what you did there - the sp_rename procedure expects 3 parameters -

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]

but you are passing a single parameter that happens to contain 3 comma delimited values.

That will not work. Instead of concatenating all the information into a single parameter, you need to pass 3 parameters to the stored procedure:

SET @OldName = CONCAT(N'dbo.serverinv_unix_system_assignements_staging.' ,@ColName)

EXEC sp_rename @OldName, 'server_name', 'COLUMN'

(Please note I've renamed @TotalName to @OldName for the sake of readability.

score 2 · Answer 2 · answered Sep 19 '18 at 19:27

The stored procedure sp_rename takes 3 parameters, by executing in the manner in which you are your variable @NewName is only getting assign to the first parameter.

        EXEC [sys].[sp_rename] @objname = N''  -- nvarchar(1035)
                             , @newname = NULL -- sysname
                             , @objtype = ''   -- varchar(13)

Your variable @NewName is getting passed in as @objname.

I think if just slightly adjust your code you'll get the desired result. Define each parameter in their own variable and pass each of them when executing the stored procedure.

        EXEC [sys].[sp_rename] @objname = @TheObjectThatNeedsChange
                             , @newname = @TheNewName
                             , @objtype = 'COLUMN'

sp_rename Procedure with a Variable Column Name

2 Answers2