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.
There is issue in parsing the JSON. If we call the proc as below, it works fine.
Please find the db<>fiddle here.