Getting Error in Stored Procedure Syntax Near the Keyword Begin.

Question

0.00/5 (No votes)

See more:

Hi Everyone ,
I have a CircleId like Gujarat,maharashtra,uttarPradesh ..and So on
I want to perform three condition:
1 st Condition :If i found the same record in database,it should not save the Record.
2 nd Condition: New Record should get Add.
3 rd Condition:if i found more than 4 record for the same CircleId it should not allow to save.
But I am getting a Error as follows .
Msg 156, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 50
Incorrect syntax near the keyword 'begin'.
Msg 156, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 56
Incorrect syntax near the keyword 'else'.

Stored Procedure is As follows:

ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG] 
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT

AS	
BEGIN	
	IF NOT EXISTS (
select count(CIRCLEID) from K2FILELOG where FILEPATH = @FILEPATH and
                                              [FILENAME] = @FILENAME and
                                              FILETYPE = @FILETYPE and
                                              FILEDATE = @FILEDATE and
                                              CIRCLEID = @CIRCLEID and
                                              TOTALROWS = @TOTALROWS and
                                              SUCCESSCOUNT = @SUCCESSCOUNT and
                                              FAILURECOUNT = @FAILURECOUNT and
                                              PROCESSED = @PROCESSED and
                                              ERROR = @ERROR and
                                              VENDORID = @VENDORID and
                                              CREATEDBY = @CREATEDBY and
                                              CREATEDON = @CREATEDON and 
                                              DELETED = @DELETED)
begin
INSERT INTO K2FILELOG
	   (FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED)
values (@FILEPATH ,@FILENAME,@FILETYPE,@FILEDATE,@CIRCLEID,@TOTALROWS,@SUCCESSCOUNT,@FAILURECOUNT,@PROCESSED,@ERROR ,@VENDORID,@CREATEDBY,@CREATEDON,@DELETED);
set @FILELOGID =@@identity
  END

ELSE if
begin
select count (CIRCLEID) from K2FILELOG with (nolock) where FILETYPE = @FILETYPE and	
                                                             FILEDATE = @FILEDATE and	
                                                             CIRCLEID = @CIRCLEID 
                                                             GROUP BY CIRCLEID having count(CIRCLEID) >= 4
END
else
begin
INSERT INTO K2FILELOG
	   (FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED)
values (@FILEPATH ,@FILENAME,@FILETYPE,@FILEDATE,@CIRCLEID,@TOTALROWS,@SUCCESSCOUNT,@FAILURECOUNT,@PROCESSED,@ERROR ,@VENDORID,@CREATEDBY,@CREATEDON,@DELETED);
set @FILELOGID =@@identity
end

END

Please Guide me .

Posted 13-Jul-14 22:10pm

R Harshal

Add a Solution

2 solutions

Solution 1

Else If should have the conditional statment you have left it blank put the condition.

Posted 13-Jul-14 22:16pm

ArunRajendra

Comments

R Harshal 14-Jul-14 4:22am

Yes I am confuse on same place.I want to perform this three Condition.
1 st Condition :If i found the same record in database,it should not save the Record.
2 nd Condition: New Record should get Add.
3 rd Condition:if i found more than 4 record for the same CircleId it should not allow to save.

will you Guide me ,Where should i need to modify the Stored Procedure.

Please Guide me.

Thanks Harshal

R Harshal 14-Jul-14 4:26am

I made some mistake in Stored Procedure which I am not able to solve it.
Please Kindly Revert.
Thanks Harshal.

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)

Sarvesh Kumar Gupta · Accepted Answer · 2014-07-13T22:45:00

Solution 2

Hi,

First Change :

SQL

ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG] (
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT
 )
AS

2nd Change:

Put a condition after else if, you have not written condition in else if.

Please make these changes, if after that you will get error then you can ask me again.

Posted 13-Jul-14 22:45pm

Sarvesh Kumar Gupta

Updated 13-Jul-14 22:46pm

v2

Comments

R Harshal 14-Jul-14 5:06am

Thanks for your reply .
But There is a twist in my stored Procedure .
I have make another stored PRocedure for saving the new Record.
In this Stored Procedure i need to check the existing Reccord.I want to perform only two condition.
1 st Condition :If i found the same record in database,it should not save the Record.
2 nd Condition:if i found more than 4 record for the same CircleId it should not allow to save.
What changes i need to make in my stored PRocedure,it gives me error :Incorrect syntax near the keyword 'ELSE'.

ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG]
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT

AS
BEGIN
IF NOT EXISTS (
select count(CIRCLEID) from K2FILELOG where FILEPATH = @FILEPATH and
[FILENAME] = @FILENAME and
FILETYPE = @FILETYPE and
FILEDATE = @FILEDATE and
CIRCLEID = @CIRCLEID and
TOTALROWS = @TOTALROWS and
SUCCESSCOUNT = @SUCCESSCOUNT and
FAILURECOUNT = @FAILURECOUNT and
PROCESSED = @PROCESSED and
ERROR = @ERROR and
VENDORID = @VENDORID and
CREATEDBY = @CREATEDBY and
CREATEDON = @CREATEDON and
DELETED = @DELETED)

ELSE

begin
select count (CIRCLEID) from K2FILELOG with (nolock) where FILETYPE = @FILETYPE and
FILEDATE = @FILEDATE and
CIRCLEID = @CIRCLEID
GROUP BY CIRCLEID having count(CIRCLEID) >= 4
END

END

R Harshal 14-Jul-14 5:40am

Hi Sarvesh.
Please kindly revert.

R Harshal 15-Jul-14 7:31am

Dear Sarvesh,
I am still waiting for your answer.Please let me know.

Thanks

R Harshal 15-Jul-14 7:36am

Anyone from the Code Project Team can tell me ,how to use If Exists statement for two condition ,As i got lot of confusion in my stored procedure ,Some the Engineer from Code MAster have closed my issue without giving the proper answer,as they says same issue was assign for number of times ,but if you check procedure are same but question are different.I dont know why they closed all those ticket .The Ticket which is open have not given reply from long time .would anyone from CodeMaster Team can help me from this situation.

Thanks in Advance
Harshal.

R Harshal 15-Jul-14 7:40am

now the problem is if the same record is not present in database then to it gives me return as 1.This could not happen.Please find the latest Stored Procedure
ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG]
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID BIGINT OUTPUT

AS
BEGIN

--IF EXISTS (
set @FILELOGID = (select count(CIRCLEID) as CircleId from K2FILELOG where FILEPATH = @FILEPATH and
[FILENAME] = @FILENAME and
FILETYPE = @FILETYPE and
FILEDATE = @FILEDATE and
CIRCLEID = @CIRCLEID and
TOTALROWS = @TOTALROWS and
SUCCESSCOUNT = @SUCCESSCOUNT and
FAILURECOUNT = @FAILURECOUNT and
PROCESSED = @PROCESSED and
ERROR = @ERROR and
VENDORID = @VENDORID and
CREATEDBY = @CREATEDBY and
CREATEDON = @CREATEDON and
DELETED = @DELETED)
--)

begin

select count (CIRCLEID) as CircleId from K2FILELOG with (nolock) where FILETYPE = @FILETYPE and
[FILENAME] = @FILENAME and
FILEPATH = @FILEPATH and
FILEDATE = @FILEDATE and
CIRCLEID = @CIRCLEID
GROUP BY CIRCLEID having count(CIRCLEID) >= 4

end

END

Sarvesh Kumar Gupta 15-Jul-14 7:48am

You are doing wrong

write in below way:

if not exist(your condition here)
begin
-- Write your query
End
else if(your condition 2nd)
begin
-- Your query
end
else
begin
--your query
end

R Harshal 15-Jul-14 7:56am

Thanks Sarvesh for your reply .
But in my stored procedure there is a twist .
The twist is I don't require if not exist in my stored procedure because i made stored procedure to check existing records in both condition ,do you get my point.
If you remember my condition as follows:
1 st Condition :If i found the same record in database,it should not save the Record and return 1.
2 nd Condition:if i found more than 4 record for the same CircleId it should not allow to save and return 1.So in my stored procedure I want to check for if exists for both the condition ,but i am confused to implement it that's why i have taken help from Code Project Team,But no one have understand my question .I hope you get my point.If anyone have dought for the same please ask me ,don't ignore it please..
Please have a look on latest Stored Procedure.

Thanks in Advance.
Harshal

Sarvesh Kumar Gupta 15-Jul-14 8:03am

Please do some pressure on mind and use of it and think over it. Please use google, you have to read sql.
if (Select Count(*) from table) > 0
return 1
else if
return 0

R Harshal 15-Jul-14 8:08am

OK. Thanks For Solving 1 st condition ,can you guide me for this condition ,as this condition is pending from long time .
Condition:if i found more than 4 record for the same CircleId it should not allow to save and return 1 in same stored procedure.

Thanks in Advance
Harshal

Sarvesh Kumar Gupta 15-Jul-14 8:35am

if (Select Count(*) from table) > 0
Begin
if (Select Count(*) from table group by Circleid) > 4
return 1
else
return 0
End
else if
return 0

R Harshal 15-Jul-14 8:53am

Thank you SarveshKumar.
Now all queries get Completed .

Thanks
Harshal