incorrect syntax near begin expecting external for Alter Procedure

[simpledeveloper]

Hi - I have a script file which does lot DDL Operations - it all included as part of the Transaction but I am getting an Error at the Begin statement of Alter Procedure as "incorrect syntax near begin expecting external" - is there any way to put the Alter Procedure as part of the Transaction Script? Here is my Script for it:

USE XXXXXX;
BEGIN TRY
    BEGIN TRANSACTION

	DROP INDEX IF EXISTS \[IX\_NOVId\] ON \[dbo\].\[Violations\]

	PRINT N'Altering \[dbo\].\[OneToManies\]...';
	ALTER TABLE \[dbo\].\[OneToManies\] ALTER COLUMN \[ChildEntity\] NVARCHAR (MAX) NULL;
	ALTER TABLE \[dbo\].\[OneToManies\] ALTER COLUMN \[ParentEntity\] NVARCHAR (MAX) NULL;


	PRINT N'Altering \[dbo\].\[usp\_Report\_ClosedReports\]...';
	ALTER PROCEDURE \[dbo\].\[usp\_Report\_ClosedReports\]
		@EnforcementSectionId INT,
		@IsPreCase            VARCHAR(3) = NULL,
		@FromDate			  DATE = '01/01/2017',
		@ToDate				  DATE = '01/01/2018'
	
	AS
		 BEGIN
		 DECLARE @IsPreCaseBool Bit;
		 SET @IsPreCaseBool = CASE WHEN @IsPreCase = '0' THEN 0 ELSE
							  CASE WHEN @IsPreCase = '1' THEN 1 ELSE null END END;

		 SELECT
			ReferenceNumber,
			CONVERT(NVARCHAR(10), CaseStartDate, 101) AS CaseStartDate,
			CONVERT(NVARCHAR(10), DateCreated, 101) AS DateCreated,
			CONVERT(NVARCHAR(10), DateUpdated, 101) AS DateUpdated,

			CONVERT(NVARCHAR(10), StatuteOfLimitationsDate, 101) AS StatuteOfLimitationsDate,       
        
			ApplicablePenalties,
			InvestigatorFullName,
			ContactName,
			CompanyCity,
			CompanyZip,
			CaseStatus,
			EnforcementSectionName,
			ISNULL(ViolationsCount, 0) AS ViolationsCount,
			Program
		FROM (SELECT cs.CaseId,
						(CASE
							 WHEN \[cst\].IsPreCase = 1
							 THEN 'I'
							 ELSE 'C'
						 END) + dbo.CIntToChar(\[cs\].CaseId, 5) AS ReferenceNumber,
						\[cs\].CaseStartDate,
						\[cs\].DateCreated,
						\[cs\].DateUpdated,
						\[cs\].StatuteOfLimitationsDate,
						\[cs\].ApplicablePenalties,
						inv.FirstName +' '+inv.LastName AS InvestigatorFullName,
						ISNULL(\[cnt\].FirstName, '')+' '+ISNULL(\[cnt\].LastName, '') AS ContactName,
						\[cnt\].Address\_City AS CompanyCity,
						\[cnt\].Address\_Zip AS CompanyZip,
						\[cst\].CaseStatusName AS CaseStatus,
						\[ens\].EnforcementSectionName,
						vl.ViolationsCount,
						\[PROG\].Program
				 FROM Cases A

[Jorgen Andersson]

You cannot use CREATE or ALTER PROCEDURE inside a transaction. But you can cheat, like this:

EXEC ('create procedure dbo.whatever
as
begin
select * from some_table
end')

Wrong is evil and must be defeated. - Jeff Ello