Click here to Skip to main content
14,669,437 members
Home » Discussions » Database
   

Database

 
Questionsplit time then group by 15 min mongodb Pin
Member 1300557210-Jan-20 21:56
MemberMember 1300557210-Jan-20 21:56 
QuestionSQL Query with a Loop Pin
Bobby Underwood6-Jan-20 4:58
MemberBobby Underwood6-Jan-20 4:58 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 5:04
mva#realJSOP6-Jan-20 5:04 
AnswerRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 5:11
MemberBobby Underwood6-Jan-20 5:11 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 5:16
mva#realJSOP6-Jan-20 5:16 
GeneralRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 5:22
MemberBobby Underwood6-Jan-20 5:22 
AnswerRe: SQL Query with a Loop Pin
Jörgen Andersson7-Jan-20 5:52
professionalJörgen Andersson7-Jan-20 5:52 
Questionincorrect syntax near begin expecting external for Alter Procedure Pin
simpledeveloper27-Dec-19 9:23
Membersimpledeveloper27-Dec-19 9:23 
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.
AnswerRe: incorrect syntax near begin expecting external for Alter Procedure Pin
Jörgen Andersson27-Dec-19 23:14
professionalJörgen Andersson27-Dec-19 23:14 
QuestionAdding multiple columns with condition Pin
simpledeveloper26-Dec-19 14:58
Membersimpledeveloper26-Dec-19 14:58 
AnswerRe: Adding multiple columns with condition Pin
Mycroft Holmes26-Dec-19 19:58
professionalMycroft Holmes26-Dec-19 19:58 
AnswerRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 3:40
professionalJörgen Andersson27-Dec-19 3:40 
GeneralRe: Adding multiple columns with condition Pin
simpledeveloper27-Dec-19 5:29
Membersimpledeveloper27-Dec-19 5:29 
GeneralRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 5:32
professionalJörgen Andersson27-Dec-19 5:32 
GeneralRe: Adding multiple columns with condition Pin
simpledeveloper27-Dec-19 8:44
Membersimpledeveloper27-Dec-19 8:44 
QuestionCrystal Report to SQL server 2012 database Pin
Member 1447460719-Dec-19 10:39
MemberMember 1447460719-Dec-19 10:39 
QuestionBackup rows tables of SQL Server database by SMO base on colums's value Pin
Member 1417466417-Dec-19 14:39
MemberMember 1417466417-Dec-19 14:39 
AnswerRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
Richard Deeming17-Dec-19 23:47
mveRichard Deeming17-Dec-19 23:47 
GeneralRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
Member 1417466417-Dec-19 23:57
MemberMember 1417466417-Dec-19 23:57 
AnswerRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
David Mujica18-Dec-19 5:58
MemberDavid Mujica18-Dec-19 5:58 
GeneralRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
Member 1417466418-Dec-19 14:35
MemberMember 1417466418-Dec-19 14:35 
GeneralRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
David Mujica19-Dec-19 9:37
MemberDavid Mujica19-Dec-19 9:37 
GeneralCode converter Pin
David Mujica19-Dec-19 9:47
MemberDavid Mujica19-Dec-19 9:47 
GeneralRe: Code converter Pin
Member 1417466419-Dec-19 16:01
MemberMember 1417466419-Dec-19 16:01 
AnswerRe: Backup rows tables of SQL Server database by SMO base on colums's value Pin
Member 1417466430-Jan-20 22:40
MemberMember 1417466430-Jan-20 22:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.