Error in sql procedure

Question

i got

Msg 156, Level 15, State 1, Procedure Flag_Account, Line 21 Incorrect syntax near the keyword 'while'. Msg 156, Level 15, State 1, Procedure Flag_Account, Line 23 Incorrect syntax near the keyword 'select'.

when i try to run

    USE [hesabat2]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create  PROCEDURE [dbo].[Flag_Account]


    AS
begin
DECLARE @Acc_link int, @Result int,@inc int,@Result2 int
SET @inc = 0
set @Result =0
DECLARE A_Cursor CURSOR FOR
   Select account_id FROM ACCOUNTS
OPEN A_Cursor

FETCH NEXT FROM A_Cursor INTO @Acc_link

WHILE (@@FETCH_STATUS =0)
BEGIN

   set @Result= (select a.account_link from ACCOUNTS a where account_id= @Acc_link
   while(@Result = 0)
   begin
   set @Result = select a.account_link from ACCOUNTS a where account_id=@Result
   SET @inc= @inc+1
   end
    update ACCOUNTS set flag_color=@inc where account_id= @Acc_link



   FETCH NEXT FROM A_Cursor INTO  @Acc_link

   END

   CLOSE A_Cursor
   DEALLOCATE A_Cursor
   end

OGHaza · Accepted Answer · 2013-11-07 10:28:00Z

2

You're missing a ) on the line

set @Result= (select a.account_link from ACCOUNTS a where account_id= @Acc_link

Could be other things too, but try that first.

answered Nov 7, 2013 at 10:28

OGHaza

4,7957 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerad Rose · Accepted Answer · 2013-11-07 10:40:36Z

2

Two problems.

This is missing a closing paren:

  set @Result= (select a.account_link from ACCOUNTS a where account_id= @Acc_link

And this is missing opening and closing parens:

  set @Result = select a.account_link from ACCOUNTS a where account_id=@Result

edited Nov 7, 2013 at 10:40

answered Nov 7, 2013 at 10:27

Jerad Rose

15.5k18 gold badges88 silver badges157 bronze badges

Comments

Madhivanan · Accepted Answer · 2013-11-07 10:31:17Z

0

The correct procedure is as follows:

create PROCEDURE [dbo].[Flag_Account]
AS 
begin 
DECLARE @Acc_link int, @Result int,@inc int,@Result2 int 
SET @inc = 0 
set @Result =0 
DECLARE A_Cursor CURSOR FOR Select account_id FROM ACCOUNTS 
OPEN A_Cursor
FETCH NEXT FROM A_Cursor INTO @Acc_link
WHILE @@FETCH_STATUS =0 
BEGIN
set @Result= (select a.account_link from ACCOUNTS a where account_id= @Acc_link 
while  @Result = 0
   begin 
      set @Result = select a.account_link from ACCOUNTS a where account_id=@Result 
     SET @inc= @inc+1 end update ACCOUNTS set flag_color=@inc where account_id= @Acc_link
     FETCH NEXT FROM A_Cursor INTO @Acc_link
   END
end
CLOSE A_Cursor 
DEALLOCATE A_Cursor 
end

edited Nov 7, 2013 at 10:31

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

answered Nov 7, 2013 at 10:30

Mukesh Sagar

313 bronze badges

Collectives™ on Stack Overflow

Error in sql procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related