CODEWITHSUNDEEP
sqlsql-servert-sql
Dipiks
Dipiks

Reputation: 3928

Using variables when calling sp_rename

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 ?

Upvotes: 3

Views: 2809

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

The problem isn't with sp_rename, per se, it's actually a problem with EXEC.

For each parameter you wish to pass, you may supply a value (a literal of some kind), a variable or the keyword DEFAULT. What you may not pass is an expression that computes a value.

[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name [ ;number ] | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH <execute_option> [ ,...n ] ]  
    }  
[;]

All this means that, if you wish to compute something, you need to do it as separate statement(s), and store the result of the computation in a variable, before the EXEC, as shown in JaydipJ's answer.

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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'

Upvotes: 5

Related Questions