Looping array Cursor not looping more than once - SQL SERVER

180 views Asked by At

I have an array which I stringified in Javascript as below

 [{PlanID:1},{PlanID:2},{PlanID:3}]

I am executing the SP as below

exec save_plan [{PlanID:1},{PlanID:2},{PlanID:3}]

I am trying to save each plan ID as each row in table plan, for which I have written the below SP I am looping the array and trying to print the PlanID(later I will write INSERT query in place of PRINT) inside the loop.

    USE [XYZ]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER Procedure save_plan
    @packages nvarchar(max)

    AS
    DECLARE
     @PlanID_FETCHED INT
     BEGIN

    DECLARE C CURSOR LOCAL FOR 
        SELECT PlanID
        FROM  OPENJSON ( @packages )  
        WITH (   
                PlanID  INT '$.PlanID'
         ) 
        OPEN C

        FETCH NEXT FROM C INTO  @PlanID_FETCHED
        WHILE @@FETCH_STATUS = 0      
        BEGIN

            PRINT @PlanID_FETCHED
        
            FETCH NEXT FROM C INTO  @PlanID_FETCHED
        END

        CLOSE C
        DEALLOCATE C

    end;

Even though there are 3 plan ID's in the array only single record is getting inserted. Cursor is not looping more than once.

1

There are 1 answers

0
sacse On

There is issue in parsing the JSON. If we call the proc as below, it works fine.

exec save_plan '[{"PlanID":1},{"PlanID":2},{"PlanID":3}]'

Please find the db<>fiddle here.