Rename a stored procedure in SQL Server

Just omit the @objtype parameter (the default is null) and it will work.

EXEC sp_rename 'sp_MyProc', 'sp_MyProcName'

You will receive the following warning, but the procedure will be renamed

Caution: Changing any part of an object name could break scripts and stored procedures.

Like others stated, you should drop and recreate the procedure.

sp_rename does not support procedures:

Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

Just create the new procedure with the same body and new name, then drop the old one.

According to the docs, 'P' is not a correct option. You should try 'OBJECT' as that seems like the closest thing to what you're trying to do. But, you should heed this warning ...

Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

Also (from the same MSDN page):

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.