Why do you not use a 'USE' statement before the drop, as you are doing later inside the CURSOR?

EXEC('USE [' + @Name + ']; drop procedure [' + @procName + ']')

Simply DECLARE the @Name variable earlier. It should work assuming all the procs are in the same database. "CREATE OR ALTER" should also complete the task, as mentioned, if you're using SQL Server 2016 and above.

Rather than EXEC (<SQL>) syntax, which is advised against, use sys.sp_executesql . YOu could then either you could prefix with the procedure with relevant database, such as TestDB.sys.sp_executesql , or you could assign the procedure name to a variable, SET @ProcName = N'TestDB.sys.sp_executesql' and then use EXEC @ProcName .

When you execute a procedure, it runs in the context of that database, so DROP PROC YourProc would drop a procedure in the procedure in the other database.

Note that in the below, you get different database IDs:

USE master;
GO
SELECT DB_ID();
EXEC tempdb.sys.sp_executesql N'SELECT DB_ID();';
DECLARE @ProcName nvarchar(500) = N'model.sys.sp_executesql';
EXEC @ProcName N'SELECT DB_ID();';

So, DROP ing a procedure would loom like this:

USE Sandbox; --Connect to a different database
GO
--Create sample proc
CREATE PROC dbo.YourProc1 AS
BEGIN
    SELECT 1 AS I;
END;
GO
--Create another sample proc
CREATE PROC dbo.YourProc2 AS
BEGIN
    SELECT 1 AS I;
END;
GO
--Switch database
USE master;
GO
EXEC Sandbox.dbo.YourProc1; --Check it works
EXEC Sandbox.dbo.YourProc2; --Check it works
GO
EXEC Sandbox.sys.sp_executesql N'DROP PROC dbo.YourProc1;'; --Drop using 3 part naming
GO
DECLARE @ProcName nvarchar(500) = N'Sandbox.sys.sp_executesql';
EXEC @ProcName N'DROP PROC dbo.YourProc2;'; --Drop using variable
GO
EXEC Sandbox.dbo.YourProc1; --Fails
GO
EXEC Sandbox.dbo.YourProc2; --Fails

For reference, this is what I wound up with:

USE [DestDB]
    GO
    /****** Object:  StoredProcedure [dbo].[USP_Update_1_DropAndCopy_Procs]    Script Date: 10/9/2023 10:58:25 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =================================================================================
    -- Author:      Me
    -- Description: Copy stored procedures from source to destination
    -- Create date: 10/06/2023
    -- =================================================================================
    ALTER PROCEDURE [dbo].[USP_Update_1_DropAndCopy_Procs] 
        @SourceServer varchar(50),
        @SourceDB varchar(50),
        @DestDB varchar(50)
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @sql NVARCHAR(MAX);
        -- Delete all procedures in DESTINATION database
        DECLARE @procName varchar(500)EXEC('DECLARE cur1 cursor FOR SELECT [name] FROM [' + @DestDB + '].[sys].[objects] WHERE type = ''p'' ')
        OPEN cur1
        FETCH NEXT FROM cur1 INTO @procName
        WHILE @@fetch_status = 0
        BEGIN
            EXEC('USE [' + @DestDB + ']; drop procedure [' + @procName + ']')
            FETCH NEXT FROM cur1 INTO @procName
        END
        CLOSE cur1
        DEALLOCATE cur1
        -- Copy all procedures from SOURCE database
        EXEC('DECLARE cur2 CURSOR FOR SELECT Definition FROM [' + @SourceServer + '].[' + @SourceDB + '].[sys].[procedures] p INNER JOIN [' + @SourceServer + '].[' + @SourceDB + '].sys.sql_modules m ON p.object_id = m.object_id')
        OPEN cur2
        FETCH NEXT FROM cur2 INTO @sql
        WHILE @@FETCH_STATUS = 0 
        BEGIN   
            SET @sql = REPLACE(@sql,'''','''''')   
            SET @sql = 'USE [' + @DestDB + ']; EXEC(''' + @sql + ''')'   
            EXEC(@sql)   
            FETCH NEXT FROM cur2 INTO @sql
        END             
        CLOSE cur2
        DEALLOCATE cur2
    END