Msg 102, Level 15, State 1, Procedure AuditView, Line 1 Incorrect syntax near 'AS'

• dinu.suniti

  SSC Enthusiast

  Points: 124

  More actions

  May 10, 2018 at 4:54 am

  #328703

  When i try to run the below procedure from my application i am getting stored procedure error as

  Msg 102, Level 15, State 1, Procedure AuditView, Line 1 Incorrect syntax near 'AS'

  Could you please help me.

  USE [production]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_Purge_PurgeBsnDate]
    @BsnDate             smalldatetime,
    @IncludeAudit        bit
AS
BEGIN
    DECLARE @ErrorReturn INT
    DECLARE @IncludePriorDates BIT
    SET NOCOUNT ON
    SET @ErrorReturn = 0
    SELECT @PriorDates = PriorDates FROM tbl_Date WHERE BsnDate = @BsnDate
    SET @ErrorReturn = @@ERROR
    IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
    IF @PriorDates = 0
    BEGIN
        /* IncludeAudit = 1 means the business date being purged is not the current
         system date. We don't want to purge the audit of current system date info
         when purging a business date of that is the same date */
        IF @IncludeAudit = 1
        BEGIN
            Exec @ErrorReturn = usp_Purge_PurgeAudit @BsnDate, @IncludePriorDates, 0
            IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        END
        DELETE FROM tbl_Version WHERE BsnDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_ErrBatch WHERE BsnDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_Transport WHERE BsnDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_ReelInfo WHERE BsnDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_TrkFieldStats WHERE SystemDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_TrkStats WHERE SystemDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_UsedBatchNumber WHERE BsnDate = @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
    END
    ELSE
    BEGIN
         Exec @ErrorReturn = usp_Purge_PurgeAudit @BsnDate, @IncludePriorDates, @IncludeAudit
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_EIVersion WHERE BsnDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_ErrBatch WHERE BsnDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_Transport WHERE BsnDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_ReelInfo WHERE BsnDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_TrkFieldStats WHERE SystemDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_TrkStats WHERE SystemDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
        DELETE FROM tbl_UsedBatchNumber WHERE BsnDate <= @BsnDate
        SET @ErrorReturn = @@ERROR
        IF @ErrorReturn <> 0 GOTO usp_PurgeBsnDate_Error
    END
    
        RETURN(0)
usp_Purge_PurgeBsnDate_Error:
    RETURN(@ErrorReturn)
END

  
  After Executing the stored procedure manually from database i am getting the below output.

  USE [AdminDB]
GO

  DECLARE@return_value int
EXEC@return_value = [dbo].[usp_PurgeBsnDate]
@BsnDate = '2018-03-16 00:00:00',
@IncludeAudit = 1

  SELECT'Return Value' = @return_value
GO

  output message
  Msg 102, Level 15, State 1, Procedure AuditView, Line 1
  Incorrect syntax near 'AS'.

  (1 row(s) affected)

• Thom A

  SSC Guru

  Points: 99143

  More actions

  May 10, 2018 at 5:07 am

  #1990001

  Welcome to SSC. You might want to respond to the comments on the identical question you posted on StackOverflow though, rather than posting a new topic on a different website: https://stackoverflow.com/questions/50270688/msg-102-level-15-state-1-procedure-auditview-line-1-incorrect-syntax-near-a. Users here are going to have the same questions.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  May 16, 2018 at 7:30 am

  #1990657

  And as your error message refers to Procedure AuditView, you'll need to look there to find the source of the problem.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 16, 2018 at 7:43 am

  #1990660

  The procedure might be in a trigger for one (or all) of the tables that you're deleting.
  BTW, we're in 2018, use TRY...CATCH blocks instead of that horrible error handling on every statement.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2