Are there ways to only replace SQL Server stored procedures when the definition has changed? I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors.
if object_id('my_proc') &gt; 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

and the other which preserves permissions
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';
    

I guess when the procedure exists and its hash code is the same as that of the new version, than there would be no need to drop and recreate or alter the procedure.
My problem is that HashBytes is limited to a maximum of 8000 bytes and I can't use it generally like
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') &gt; 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) &lt;&gt; 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end 

For procedures whose definition are longer than 4000 Characters. Any proposals to handle these cases in a similar way?
Edit:
It is not only that I want to avoid flushing of cached plans.
I also have to cope with different customers having different versions of a stored procedure where I only want to replace one of these variants by a newer version.
Permissions shouldn't be assigned to the stored procedure

Your stored procedure should sit in a schema, permissions are on the schema (granted to roles). This way, new objects in that schema have permissions automatically. No single object should have permissions

So either way would be OK.

See these other DBA questions:


Set permissions on all objects for SQL users
What standard should I follow when naming tables and views?
Schema design - best practices?


FYI, object_id is signed int so use "does not equal"

if object_id('my_proc') &lt;&gt; 0 drop procedure my_proc


Edit, nvarchar(max) is comparable directly

if object_Id('my_proc') &lt;&gt; 0
    drop proc ThenewProc
GO
CREATE PROC ThenewProc
...
GO

IF object_definition(object_id('my_proc')) &lt;&gt; 
         object_definition(object_id('ThenewProc')) &lt;&gt; 0
BEGIN
    drop procedure my_proc
    drop proc ThenewProc
END
GO
CREATE PROC my_proc
...
GO