Incorrect syntax near the keyword `End` in stored procedure

Question

When I trying to execute following stored procedure it gives this error

Msg 156, Level 15, State 1, Procedure TT_SP_UpdateTask, Line 43
Incorrect syntax near the keyword 'end'.

The End refers to the end which near to if (@Status = 'Developing') in the following procedure.

I checked the order of begins and ends again and again, but I couldn't find the reason for the error.

This is my stored procedure.

ALTER PROCEDURE [dbo].[TT_SP_UpdateTask]

@EstimateTime decimal(18,2),
@Status varchar(10),
@TaskAssignId varchar(30),
@IsDone bit

AS

BEGIN
if(@IsDone = 1)
begin
   UPDATE [TT_TaskAssign]
      SET 
        DevFinish = getdate(),
        Status = @Status,
        IsDone = @IsDone
      WHERE AssignID = @TaskAssignId

   UPDATE dbo.TT_TaskAsignKPI
     SET 
       actTime = (select convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%3600/60)+':'+convert(varchar(5),(DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%60)) as [hh:mm:ss], 
       actTimeNum= (SELECT DATEDIFF(SECOND, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), GETDATE()) AS SecondDiff, 
       KPINum= (SELECT DATEDIFF(SECOND,(SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE()) AS SecondsKPI,
       KPI= (select convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%3600/60)+':'+convert(varchar(5),(DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%60)) as [HH:MM:SS]   
     WHERE AssignID=@TaskAssignId 

end --(This is the end which cause for the error)

if(@Status='Developing')
begin
   UPDATE [TT_TaskAssign]
      SET 
        EstimateTime = @EstimateTime,
        Status = @Status,
        IsDone = @IsDone,
        DevStart = getdate() 
      WHERE AssignID = @TaskAssignId

   UPDATE dbo.TT_TaskAsignKPI
      SET 
        actAsignTime = getdate()+@EstimateTime/24
      WHERE AssignID=@TaskAssignId 

end

END

gotqn gotqn · Accepted Answer · 2015-04-08T06:29:19

Use the following update statement. There were few syntax errors:

   UPDATE dbo.TT_TaskAsignKPI
     SET 
       actTime = convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%3600/60)+':'+convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%60) 
       ,actTimeNum= DATEDIFF(SECOND, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), GETDATE())
       ,KPINum= DATEDIFF(SECOND,(SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())
       ,KPI= convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%3600/60)+':'+convert(varchar(5),(DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%60))
     WHERE AssignID=@TaskAssignId

Incorrect syntax near the keyword `End` in stored procedure

2 Answers