DECLARE @OriginalProcName SYSNAME = 'Sp_DailyStoreExpense';
DECLARE @TodayPrefix NVARCHAR(20) = CONVERT(VARCHAR(8), GETDATE(), 112); -- YYYYMMDD
DECLARE @NewProcName SYSNAME = @OriginalProcName + '_' + @TodayPrefix;
DECLARE @Definition NVARCHAR(MAX);
DECLARE @NewDefinition NVARCHAR(MAX);

-- Get the original procedure definition
SELECT @Definition = OBJECT_DEFINITION(OBJECT_ID(@OriginalProcName));

IF @Definition IS NOT NULL
BEGIN
    SET @NewDefinition = @Definition;

    -- Replace different common variations (case-sensitive)
    SET @NewDefinition = REPLACE(@NewDefinition,
        'CREATE PROCEDURE [dbo].[' + @OriginalProcName + ']',
        'CREATE PROCEDURE [dbo].[' + @NewProcName + ']');

    SET @NewDefinition = REPLACE(@NewDefinition,
        'CREATE PROC [dbo].[' + @OriginalProcName + ']',
        'CREATE PROCEDURE [dbo].[' + @NewProcName + ']');

    SET @NewDefinition = REPLACE(@NewDefinition,
        'CREATE PROCEDURE ' + @OriginalProcName,
        'CREATE PROCEDURE [dbo].[' + @NewProcName + ']');

    SET @NewDefinition = REPLACE(@NewDefinition,
        'CREATE PROC ' + @OriginalProcName,
        'CREATE PROCEDURE [dbo].[' + @NewProcName + ']');

    -- Optionally drop the target proc if it already exists
    IF OBJECT_ID(@NewProcName) IS NOT NULL
        EXEC('DROP PROCEDURE [dbo].[' + @NewProcName + ']');

    -- Execute the new definition
    EXEC sp_executesql @NewDefinition;

    PRINT 'Procedure cloned as [' + @NewProcName + ']';
END
ELSE
BEGIN
    PRINT 'Procedure [' + @OriginalProcName + '] not found or is encrypted.';
END