-3

I am getting an error in stored procedure as follows:

Msg 156, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 27
Incorrect syntax near the keyword 'if'.

Msg 102, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 42
Incorrect syntax near '>'.

Msg 156, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 55
Incorrect syntax near the keyword 'else'.

Msg 156, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 58
Incorrect syntax near the keyword 'ELSE'.

Msg 102, Level 15, State 1, Procedure K2_CHECKENTRYINFILELOG, Line 61
Incorrect syntax near 'end'.

This is my 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
if  
begin
   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 > 0
begin
if 
   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

    return 1;
end
else 
    return 0;
END
ELSE If
begin
    return o;
end

Kindly help me please.

Thanks in advance

Harshal

closed as off-topic by Mark Storey-Smith, Paul White, RolandoMySQLDBA, ypercubeᵀᴹ, Aaron Bertrand Jul 15 '14 at 18:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, Paul White, RolandoMySQLDBA, ypercubeᵀᴹ, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Well, you need AS after the list of parameters, for starters. Also good to enclose the body of the procedure in BEGIN and END (more for visual aesthetics than anything). – Aaron Bertrand Jul 15 '14 at 13:52
4

It is telling you there are syntax errors in your procedure, and there are.

I can't quite tell what you are trying to do as the syntax is pretty odd...

The first error refers to having an IF statement with no test clause. These should be of the form IF <condition> <statement> or IF <condition> BEGIN <statetments> END - having no condition (the BEGIN directly following the IF) simply isn't valid syntax.

The next is referring to DELETED = @DELETED >0 which again simply isn't valid syntax in TSQL. If you are trying to set the value for the output DELETED to true if the variable @DELETED is greater than zero the you want something like DELETED = CASE WHEN @DELETED>0 THEN 1 ELSE 0 END or to make sure that the output value type is boolean DELETED = CAST(CASE WHEN @DELETED>0 THEN 1 ELSE 0 END AS BIT).

I'll not go through each error as that syntax is so far "off" that I can't tell what you are actually trying to achieve and you've not described the intended process at all. I suggest that you read up on TSQL syntax.

  • I want to prove this 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 your team .Please guide me in my stored procedure.Please tell me what changes i need to do.please David help me out Thanks in advance Harshal – HPR Jul 15 '14 at 13:34
  • 1
    You also have a return o at the end instead of a return 0. – Kenneth Fisher Jul 15 '14 at 13:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.