CODEWITHSUNDEEP
sqlsql-server
VSR
VSR

Reputation: 3

Iam getting following error everytime i try to run my proc ...Incorrect syntax near 'END'

CREATE PROCEDURE [dbo].[SP_StoredProc_Status] @Procname varchar(50)
AS

BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT 
    SET NOCOUNT ON;
    SET DATEFORMAT dmy;

    BEGIN TRY
        -- Check if the procedure is already running.
        -- This check is MANDATORY in all stored procs.
        IF EXISTS (
                SELECT *
                FROM IMP_StoredProcRunTracking
                WHERE StoredProcName = @Procname
                    AND DATEADD(hh, 1, ISNULL(StartedAt, DATEADD(year, - 50, GETDATE()))) > GETDATE()
                )
            BEGIN
                RETURN
            END
        ELSE
            BEGIN
                MERGE INTO IMP_StoredProcRunTracking AS Target
                USING (
                    SELECT @procname StoredProcName
                    ) AS Source
                    ON Target.StoredProcName = Source.StoredProcName
                WHEN MATCHED
                    THEN
                        UPDATE
                        SET Target.StartedAt = GETDATE()
                WHEN NOT MATCHED
                    THEN
                        INSERT (
                            StoredProcName
                            ,StartedAt
                            )
                        VALUES (
                            @Procname
                            ,GETDATE()
                            );
                        end

    END

Upvotes: 0

Views: 31

Answers (2)

jamir
jamir

Reputation: 62

Create PROCEDURE [dbo].[SP_StoredProc_Status] @Procname varchar(50)
AS

BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT 
    SET NOCOUNT ON;
    SET DATEFORMAT dmy;

    BEGIN TRY
        -- Check if the procedure is already running.
        -- This check is MANDATORY in all stored procs.
        IF EXISTS (
                SELECT *
                FROM IMP_StoredProcRunTracking
                WHERE StoredProcName = @Procname
                    AND DATEADD(hh, 1, ISNULL(StartedAt, DATEADD(year, - 50, GETDATE()))) > GETDATE()
                )
            BEGIN
                RETURN
            END
        ELSE
            BEGIN
                MERGE INTO IMP_StoredProcRunTracking AS Target
                USING (
                    SELECT @procname StoredProcName
                    ) AS Source
                    ON Target.StoredProcName = Source.StoredProcName
                WHEN MATCHED
                    THEN
                        UPDATE
                        SET Target.StartedAt = GETDATE()
                WHEN NOT MATCHED
                    THEN
                        INSERT (
                            StoredProcName
                            ,StartedAt
                            )
                        VALUES (
                            @Procname
                            ,GETDATE()
                            );
                        end


    END TRY

    BEGIN CATCH
    -- Execute error retrieval routine.

    END CATCH

END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You are missing an end try/begin catch/end catch to actually handle the exception handling that you have started.

Upvotes: 1

Related Questions