CODEWITHSUNDEEP
sqlsql-server
eiu165
eiu165

Reputation: 6271

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

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'.

Upvotes: 3

Views: 1411

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Try:

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

IMHO you should never call a stored procedure without EXEC.

Upvotes: 5

Akash KC
Akash KC

Reputation: 16310

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

Upvotes: 3

Related Questions