May 10, 2018 at 4:54 am
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)
May 10, 2018 at 5:07 am
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
May 16, 2018 at 7:30 am
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)
May 16, 2018 at 7:43 am
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply