Msg 102, level 15, state 1, procedure auditview, line 1 incorrect syntax near 'as'

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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.

SQL

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
		/* 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

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.

SQL

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)

Posted 10-May-18 0:33am

suniti dinesh

Updated 17-Nov-20 18:36pm

v3

Add a Solution

Comments

CHill60 10-May-18 6:48am

Please clarify when you get the error - is it when you are amending the stored procedure or when you are attempting to run it?
Have you tried dropping the procedure and recreating it rather than amending it?
You state that you get some output after executing the stored procedure manually but you have only listed the code that you use to call the procedure.

suniti dinesh 10-May-18 7:03am

Hi Chill, Thanks for your reply. when i try to the procedure i am getting error. I tried to drop procedure and re-created but i am getting the same error message like below.

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)

suniti dinesh 10-May-18 7:03am

Hi Chill, Thanks for your reply. when i try to the procedure i am getting error. I tried to drop procedure and re-created but i am getting the same error message like below.

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)

CHill60 10-May-18 7:44am

You do realise you still haven't said when the error occurs .. "when i try to the procedure" ... do what to the procedure. And you again quote the code you use to *run* the procedure.
At least now we can see that the error is not in the procedure you gave the code for, it's in the procedure "AuditView"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-05-10T01:11:00

Solution 1

Based on the error message the problem is in procedure AuditView. Since you call other procedures from this procedure the chances are that somewhere in the call chain AuditView is called or created but the syntax is broken.

Based on the output usp_Purge_PurgeAudit would probably be the place I'd started looking from.

Posted 10-May-18 1:11am

Wendelius

Comments

Wendelius 10-May-18 7:28am

Looking the code, it seems like it's trying to create the view with the following statement

CREATE VIEW AuditView 
AS 
UNION ALL
SELECT * FROM TableName

Why the UNION ALL is present?

To investigate this more efficiently, add a command

PRINT @ViewSQL

just befire you try to create the view. This way you see what's happening.

suniti dinesh 11-May-18 7:28am

Hi Wndelius,
Thanks for the update. I found the place where I am getting error.
There is an syntactical error in the usp_Purge_PurgeAudit procedure. I have fixed it and working fine.