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 AS [cs]
JOIN vw_CasePrograms AS PROG ON PROG.CaseId = [cs].CaseId
LEFT JOIN CaseAssignedToInvestigators ctoi ON ctoi.CaseId = cs.CaseId
LEFT JOIN Contacts AS [cnt] ON [cnt].ContactId = [cs].CaseCompanyId
LEFT JOIN CaseStatus AS [cst] ON [cst].CaseStatusId = [cs].CaseStatusId
LEFT JOIN (select Id, UG.GroupId, FirstName, LastName
from AspNetUsers AS U
join UserGroup AS UG on UG.UserId = U.Id)
AS inv ON (inv.Id = ctoi.UserId AND CST.IsPreCase <> 1)--AND INV.GroupId = 10)
OR (inv.Id = cs.AssignedToInspectorId AND cst.IsPreCase = 1)-- only on investigation
LEFT JOIN EnforcementSections AS [ens] ON [ens].EnforcementSectionId = [cs].EnforcementSectionId
LEFT JOIN (
SELECT COUNT(1) AS ViolationsCount,
v.CaseId
FROM dbo.Violations v
GROUP BY v.CaseId
) vl ON vl.CaseId = cs.CaseId
WHERE([cst].IsPreCase = @IsPreCaseBool
OR @IsPreCaseBool IS NULL)
AND [cst].IsCaseClosed = 1
AND [cs].DateUpdated BETWEEN @FromDate and @ToDate
AND [cs].EnforcementSectionId = @EnforcementSectionId) AS QR
GROUP BY
ReferenceNumber,
CaseStartDate,
DateCreated,
DateUpdated,
StatuteOfLimitationsDate,
ApplicablePenalties,
InvestigatorFullName,
ContactName,
CompanyCity,
CompanyZip,
CaseStatus,
EnforcementSectionName,
ViolationsCount,
Program
END;
PRINT N'Altering [dbo].[SP_ViolationTypesBranched]...';
ALTER PROCEDURE [dbo].[SP_ViolationTypesBranched]
(@Types as NVARCHAR(max) = '',
@Search AS NVARCHAR(MAX) = '')
AS
BEGIN
select
BranchId
,BranchName
,ViolationTypeName
,BranchViolationName
,ViolationTypeCode
,ViolationTypeId
,ViolationTypeSortOrder
,UploadedPhotographCategoryGroup
,Id = null
--Sections
FROM (
select
VT.BranchId,
BR.Name AS BranchName,
BR.Name + ' - ' + ViolationTypeName AS BranchViolationName,
ViolationTypeName,
ViolationTypeCode,
VT.ViolationTypeId,
ViolationTypeSortOrder ,
UploadedPhotographCategoryGroup,
(SELECT
ES.EnforcementSectionName + ', '
FROM EnforcementSections AS ES
WHERE ES.BranchId = VT.BranchId
FOR XML PATH('')) AS Sections
from BranchViolationTypes AS VT
JOIN Branches AS BR ON BR.BranchId = VT.BranchId
--JOIN EnforcementSections AS FS ON FS.BranchId = BR.BranchId
JOIN ViolationTypes AS VV ON VV.ViolationTypeId = VT.ViolationTypeId
) AS X
WHERE dbo.DynoSearch(
ISNULL(CAST(X.BranchName AS NVARCHAR(MAX)),'') +
ISNULL(CAST(X.ViolationTypeCode AS NVARCHAR(MAX)),'') +
ISNULL(CAST(X.ViolationTypeName AS NVARCHAR(MAX)),'') +
ISNULL(CAST(x.Sections AS NVARCHAR(MAX)),''),
@Search) = 1
END
ALTER TABLE [dbo].[ViolationTypeNOVs] WITH CHECK CHECK CONSTRAINT [FK_dbo.ViolationTypeNOVs_dbo.ViolationTypes_ViolationTypeId];
PRINT N'Update complete.';
ROLLBACK TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
Any help please? Thank you.
|