USE [SomeDatabase]

BEGIN TRANSACTION
GO
IF ( OBJECT_ID(N'[dbo].[ProcedureToUpdate]') IS NOT NULL )
    DROP PROCEDURE [dbo].[ProcedureToUpdate]
GO
IF @@ERROR <> 0
    AND @@TRANCOUNT > 0
    ROLLBACK TRAN
GO
IF @@TRANCOUNT = 0
    BEGIN
        --Flag that the procedure hasn't been dropped, begin another transaction
        BEGIN TRANSACTION
    END
GO

PRINT 'Creating procedure'
GO

CREATE PROCEDURE [dbo].[ProcedureToUpdate]
AS
    SELECT  *
    FROM    [dbo].[SomeTable] st
            INNER JOIN [dbo].[AnotherTable] at ON st.PK = at.fk

GO

IF @@ERROR <> 0
    AND @@TRANCOUNT > 0
    ROLLBACK TRAN
GO
IF @@TRANCOUNT = 0
    BEGIN
        --Flag that the procedure hasn't been created, begin another transaction
        BEGIN TRANSACTION
    END
GO

Using con As New SqlConnection(ConnectionString)
    con.Open()
    Dim cmd As SqlCommand = con.CreateCommand()
    Dim transaction As SqlTransaction = con.BeginTransaction("Upgrade")
    cmd.Connection = con
    cmd.Transaction = transaction
    Try
      For Each transactionalScript In transactionalScripts
        cmd.CommandText = transactionalScript
        Dim result = cmd.ExecuteNonQuery()
      Next
      transaction.Commit()
    Catch ex As Exception
      Log(String.Format("{0} : Script Failed", DateTime.Now.ToString()))
      Log(String.Format("{0} : Reason: {1}", DateTime.Now.ToString(), ex.Message))
      transaction.Rollback("Upgrade")
      Log(String.Format("{0} : Database rolled back", DateTime.Now.ToString()))
    End Try
  End Using