Using variables when calling sp_rename

Question

I try to make a stored proc which will

1. Drop a primary key
2. Rename the column name where the primary key was set
3. Create the new primary key

I'm struggling with the point number 2.

I'm trying to rename the column with sp_rename with the parameters passed to the stored proc like this:

EXEC sp_rename '[' + @SCHEMA + '].[' + @TABLE + '].[ID]' , 'Id', 'COLUMN'

But this way I got this error:

Procedure or function 'sp_RENAME' expects parameter '@newname', which was not supplied.

How can I use sp_rename with parameters ?

Answer 1

Try like this

DECLARE @SCHEMA NVARCHAR(30)='your schema name'
DECLARE @TABLE NVARCHAR(30)='table Name'
DECLARE @OLD NVARCHAR(30) = '[' + @SCHEMA + '].[' + @TABLE + '].[ID]'
EXEC sp_rename  @OLD, 'Id'