sql server if block errors when running a stored proc inside - gives a syntax error

Question

I would like to have a script for deployment which is rerunable. So I check if the table is there before renaming it.

IF EXISTS ( SELECT  * FROM    sys.objects  WHERE   object_id = OBJECT_ID(N'[dbo].[Schema]')  AND type IN ( N'U' ) )  
BEGIN
sp_rename [Schema], [SchemaInfo] 
END

The error is

Incorrect syntax near 'sp_rename'.

Aaron Bertrand · Accepted Answer · 2012-07-19 19:57:00Z

5

Try:

EXEC sp_rename N'Schema', N'SchemaInfo';

IMHO you should never call a stored procedure without EXEC.

answered Jul 19, 2012 at 19:57

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

Comments

Akash KC · Accepted Answer · 2012-07-19 19:56:45Z

3

Try with EXEC statement in followng way:

IF EXISTS ( SELECT  * FROM    sys.objects  WHERE   object_id = OBJECT_ID(N'[dbo].[Schema]')  AND type IN ( N'U' ) )  
BEGIN
EXEC sp_rename [Schema], [SchemaInfo] 
END

answered Jul 19, 2012 at 19:56

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

sql server if block errors when running a stored proc inside - gives a syntax error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related