Looping array Cursor not looping more than once - SQL SERVER

Question

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.

I have to ask, but why are you using a `CURSOR` at all here? What is the purpose of this SP, as it just Prints; which doesn't actually serve any benefit apart from a debugging aid. — Thom A, Jul 14 '20 at 11:21
Also, your sample JSON isn't valid. Trying to parse it will result in an error: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=993c5895d2bf6b4bcabd2f78fec0918c) — Thom A, Jul 14 '20 at 11:24
Why do you want to insert planid's from input JSON row by row. If you fix the JSON input (`PlanID` should be `"PlanID"`), statement `INSERT INTO plan (PlanID) SELECT PlanID FROM OPENJSON(REPLACE(@packages, N'PlanID', N'"PlanID"')) WITH (PlanID int '$.PlanID') ` is enough. — Zhorov, Jul 14 '20 at 11:32

score 1 · Answer 1 · answered Jul 14 '20 at 12:34

1

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.

answered Jul 14 '20 at 12:34

sacse

3,634
2
15
24

Looping array Cursor not looping more than once - SQL SERVER

1 Answers1