CODEWITHSUNDEEP
sqlsql-serverstored-proceduresrename
Felipe Oriani
Felipe Oriani

Reputation: 38598

Stored procedure to rename other stored procedure!

I'm using SQL Server and I've been developing a stored procedure to rename (actually, add a prefix in its name) other stored procedures. I have been trying to do this but I don't know why I'm getting this error.

Take a look at my code:

DECLARE @_currentProcedure AS VARCHAR(300)
DECLARE @_newProcedure AS VARCHAR(300)

SET @_currentProcedure = '[dbo].[' + @ProcedureName + ']'
SET @_newProcedure = @Prefx + @ProcedureName 

-- check if this procedure exists
IF (EXISTS(SELECT * 
           FROM [dbo].[sysobjects] 
           WHERE ID = object_id(@_currentProcedure ) 
              AND OBJECTPROPERTY(id, N'IsProcedure') = 1
         )) 
BEGIN
        -- rename it (doesn't work)
        EXEC sp_rename @_currentProcedure , @_newProcedure , 'OBJECT'
        GO

END

And when I try to compile this, I get a error saying

Wrong syntax near 'OBJECT'
Wrong syntax near 'END'

Something like this.

What can I do to fix this error and make this procedure works!?

Thank you!

Upvotes: 0

Views: 1940

Answers (2)

Rahul
Rahul

Reputation: 77846

Well that GO should be at end .. that is it should be 

BEGIN 
EXEC sp_rename @_currentProcedure , @_newProcedure , 'OBJECT'  
END
GO

Upvotes: 3

Mark
Mark

Reputation: 1088

_newProcedure is set from @Procedure name, so it is missing the brackets and the dbo. that you've added to _currentProcedure.

Try:

 SET @_newProcedure = '[dbo].[' + @Prefx + @ProcedureName + ']'

Upvotes: 0

Related Questions