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 [AdminDB]
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
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
output msg
Msg 102, Level 15, State 1, Procedure AuditView, Line 1
Incorrect syntax near 'AS'.
(1 row(s) affected)
What I have tried:
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
Msg 102, Level 15, State 1, Procedure AuditView, Line 1
Incorrect syntax near 'AS'.
(1 row(s) affected)