RunScript Stored Procedure

SP_RunScript.sql

IF ( OBJECT_ID('dbo.RunScript') IS NOT NULL ) 
   DROP PROCEDURE dbo.RunScript
GO

CREATE PROCEDURE dbo.RunScript
        @scriptUrl nvarchar(max)
AS 
BEGIN 
    SET NOCOUNT ON 

    DECLARE @retval nvarchar(max)
    DECLARE @sql nvarchar(500) = NULL;
    DECLARE @scriptName nvarchar(600);
    DECLARE @paramDef nvarchar(500) = N'@retvalOUT nvarchar(max) OUTPUT';

    SET @scriptName = CASE
        WHEN charindex('\', @scriptUrl) = 0 THEN @scriptUrl
        ELSE reverse(left(reverse(@scriptUrl), charindex('\', reverse(@scriptUrl)) - 1)) END

    BEGIN TRY
        SELECT @sql = 'SELECT @retvalOUT = BulkColumn FROM OPENROWSET(BULK ''' + @scriptUrl + ''', SINGLE_CLOB) AS Contents'

        exec sp_executesql @sql, @paramDef, @retvalOUT = @retval OUTPUT;
    END TRY
    BEGIN CATCH
        -- try to run script as text
        SET @retval = @scriptUrl
    END CATCH
    
    DECLARE @count int
    select @count = count(Name) from Script where Name = @scriptName and RanOk = 1

    IF (@count = 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION 

            EXEC(@retval)

            COMMIT TRANSACTION
            
            INSERT INTO [dbo].[Script] ([Name], [Date], [RanOk], [Observation])
            VALUES (@scriptName, GETDATE(), 1, NULL)

            PRINT 'SCRIPT [' + @scriptName +'] COMPLETED SUCCESSFULLY'
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 
                ROLLBACK TRANSACTION

            DECLARE @error nvarchar(255)
            set @error = ERROR_MESSAGE()

            INSERT INTO [dbo].[Script] ([Name], [Date], [RanOk], [Observation])
            VALUES (@scriptName, GETDATE(), 0, @error)

            PRINT 'SCRIPT [' + @scriptName +'] FAILED'
            PRINT 'Error:' + @error 
        END CATCH
    END
    ELSE
    BEGIN
        PRINT 'SCRIPT [' + @scriptName +'] WAS PREVIUSLY RUN'
    END
END 

GO