CODEWITHSUNDEEP
sql-serverwhile-loop
user2964296
user2964296

Reputation: 15

Error in sql procedure

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

Upvotes: 0

Views: 82

Answers (3)

Jerad Rose
Jerad Rose

Reputation: 15503

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

Upvotes: 2

Mukesh Sagar
Mukesh Sagar

Reputation: 31

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

Upvotes: 0

OGHaza
OGHaza

Reputation: 4795

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.

Upvotes: 2

Related Questions