sp_rename programming error in SQL Server

Question

Can you please advise on how to write this code properly:

declare @str nvarchar(50)
Set @str = '[table1].[column_name1]'
sp_RENAME @str, 'column2', 'COLUMN'

It currently gives an error

Incorrect syntax near 'sp_RENAME'

This should be equivalent with:

sp_RENAME '[table1].[column1]', '[column2]', COLUMN

that is used to rename a column from a table.

I need to use the first method because this happens as a general step in a procedure.

Thanks a lot!

Always use EXEC when calling stored procedures. The lazier syntax you're using is eligible for the first statement in the batch, but not later. — Aaron Bertrand
– Aaron Bertrand, Commented Nov 25, 2012 at 0:00
Hi Aaron, it's good to know that; not sure what you refer to exactly by lazy syntax? — Sam
– Sam, Commented Nov 25, 2012 at 0:26
Typing sp_something is definitely lazier than EXEC sp_something, no? — Aaron Bertrand
– Aaron Bertrand, Commented Nov 25, 2012 at 0:26
Oh, I see now, thanks for explaining. In my case it was just not knowing the right syntax. — Sam
– Sam, Commented Nov 25, 2012 at 12:32
Keep in mind that comments here are not always just for you - they are often for other (future) readers as well. And this is very common lazy syntax (this issue comes up a lot - here is an example), even if in your specific case it wasn't explicitly lazy. — Aaron Bertrand
– Aaron Bertrand, Commented Nov 25, 2012 at 14:39

fnurglewitz · Accepted Answer · 2012-11-24 23:54:39Z

3

I just tried your code and had no problems at all, could it be that you forgot to use "execute" to call the stored procedure?

declare @str nvarchar(50)
Set @str = '[table1].[column_name1]'
execute sp_RENAME @str, 'column2', 'COLUMN'

answered Nov 24, 2012 at 23:54

fnurglewitz

2,12714 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you tr3! Indeed I had forgotten to use the execute keyword.