How to pass json as input parameter to the Stored procedure

Hi All,

I have the below stored procedure which is still in development phase, as of now i am passing input as hardcoding it in the stored procedure but i have to pass it as input parameter.

here is the code of my stored proc, please suggest.

CREATE OR ALTER PROCEDURE [dbo].[upsert_evnt_rsvp_dtl_tmp] @evnt_id int, @QUES_ANS_DTL nvarchar(max)
AS
BEGIN
DECLARE @CurQSTN CURSOR;
DECLARE @CurANS CURSOR;
DECLARE @QSTN_NBR int;
DECLARE @QSTN_DESC NVARCHAR(400);
DECLARE @MNDTR_QSTN_IND bit;
DECLARE @ANS_JSON nvarchar(max);
DECLARE @ACTV_QSTN_IND bit;
DECLARE @ANS_NBR int;
DECLARE @ANS_DESC NVARCHAR(400);
DECLARE @ACTV_ANS_IND bit;

SET @QUES_ANS_DTL = N'{
"questions":[
{
"qstrnNbr": 2,
"qstrDesc": "Are you attendig the event ?",
"mndtrQstnrInd": true,
"actvQstnrInd": true,
"answers": [
{
"ansNbr": 1,
"ansDesc": "Yes",
"actvAnsInd": true
},
{
"ansNbr": 2,
"ansDesc": "No",
"actvAnsInd": true
}
]
},
{
"qstrnNbr": 3,
"qstrDesc": "What do you want for lunch ?",
"mndtrQstnrInd": false,
"actvQstnrInd": true,
"answers": [
{
"ansNbr": 3,
"ansDesc": "Chicken",
"actvAnsInd": true
},
{
"ansNbr": 4,
"ansDesc": "Pizza",
"actvAnsInd": true
},
{
"ansNbr": 5,
"ansDesc": "Salad",
"actvAnsInd": true
},
{
"ansNbr": 6,
"ansDesc": "Bread",
"actvAnsInd": true
}
]
}]
}';

BEGIN TRAN
--Set cursor for Questions
SET @CurQSTN = CURSOR FOR
SELECT * FROM OpenJson(@QUES_ANS_DTL,'$.questions')
WITH (
QSTN_NBR int '$.qstrnNbr',
QSTN_DESC varchar(200) '$.qstrDesc',
ACTV_QSTN_IND bit '$.actvQstnrInd',
MNDTR_QSTN_IND bit '$.mndtrQstnrInd',
ANS_JSON nvarchar(max) '$.answers' AS JSON
)

-- Perform the Audit for all the impacted tables

--Open cursor
OPEN @CurQSTN 
--Fetch it
FETCH NEXT FROM @CurQSTN 
INTO @QSTN_NBR,@QSTN_DESC,@ACTV_QSTN_IND,@MNDTR_QSTN_IND,@ANS_JSON

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '-----------------'
	PRINT '@QSTN_NBR:' + cast(@QSTN_NBR as varchar)
	PRINT '@QSTN_DESC:' + @QSTN_DESC
	PRINT '@ACTV_QSTN_IND:' + cast(@ACTV_QSTN_IND as varchar)
	PRINT '@MNDTR_QSTN_IND:' + cast(@MNDTR_QSTN_IND as varchar)
	PRINT CHAR(13)+CHAR(10)

	-- Update first  		
	UPDATE dbo.QSTN SET QSTN_DESC = @QSTN_DESC, MNDTR_QSTN_IND = @MNDTR_QSTN_IND, UPDT_TSTP = SYSDATETIME(), UPDT_USR_ID = 'TEST'  WHERE QSTN_NBR=@QSTN_NBR

	IF @@ROWCOUNT = 0
		--If fails then insert
		BEGIN
		   INSERT INTO  dbo.QSTN (QSTN_DESC,MNDTR_QSTN_IND,CRTE_TSTP,CRTE_USR_ID) 
		   VALUES (@QSTN_DESC, @MNDTR_QSTN_IND, SYSDATETIME(),'TEST')  
		   select @QSTN_NBR =   (SELECT SCOPE_IDENTITY())
		   --PRINT @QSTN_NBR
		   PRINT '@QSTN_NBR:' + cast(@QSTN_NBR as varchar)
		   --INSERT INTO dbo.EVNT_RSVP (EVNT_ID, QSTN_NBR, ACTV_QSTN_IND, CRTE_TSTP, CRTE_USR_ID) VALUES (@evnt_id, @QSTN_NBR,@ACTV_QSTN_IND, SYSDATETIME(), 'TEST')
		END
	ELSE
		BEGIN
		UPDATE dbo.EVNT_RSVP SET ACTV_QSTN_IND = @ACTV_QSTN_IND, UPDT_TSTP = SYSDATETIME(), UPDT_USR_ID = 'TEST'  WHERE EVNT_ID = @evnt_id and QSTN_NBR = @QSTN_NBR
		IF @@ROWCOUNT = 0
		INSERT INTO dbo.EVNT_RSVP (EVNT_ID, QSTN_NBR, ACTV_QSTN_IND, CRTE_TSTP, CRTE_USR_ID) VALUES (@evnt_id, @QSTN_NBR,@ACTV_QSTN_IND, SYSDATETIME(), 'TEST')
		END
	--Set cursor for Answers
	SET @CurANS= CURSOR FOR
	SELECT * FROM OpenJson(@ANS_JSON)
	WITH (
		ANS_NBR int '$.ansNbr',
		ANS_DESC varchar(200) '$.ansNbr',
		ACTV_ANS_IND bit '$.actvAnsInd')

	-- Open cursor
	OPEN @CurANS 
	--Fetch it
	FETCH NEXT FROM @CurANS 
	INTO @ANS_NBR, @ANS_DESC, @ACTV_ANS_IND

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '-----------------'
		PRINT '@ANS_NBR:' + cast(@ANS_NBR as varchar)
		PRINT '@ANS_DESC:' + @ANS_DESC
		PRINT '@ACTV_ANS_IND:' + cast(@ACTV_ANS_IND as varchar)
		PRINT CHAR(13)+CHAR(10)

		-- Update answer first
		UPDATE dbo.ANS_DTL SET ANS_DESC = @ANS_DESC, ACTV_ANS_IND = @ACTV_ANS_IND, UPDT_TSTP = SYSDATETIME(), UPDT_USR_ID = 'TEST'  WHERE QSTN_NBR=@QSTN_NBR AND ANS_NBR = @ANS_NBR

		-- if fails then insert
		IF @@ROWCOUNT = 0
			INSERT INTO dbo.ANS_DTL (QSTN_NBR, ANS_NBR,ANS_DESC, ACTV_ANS_IND, CRTE_TSTP, CRTE_USR_ID) 
			VALUES (@QSTN_NBR,@ANS_NBR ,@ANS_DESC, @ACTV_ANS_IND, SYSDATETIME(),'TEST')

		--Fetch the next record
		FETCH NEXT FROM @CurANS 
		INTO @ANS_NBR, @ANS_DESC, @ACTV_ANS_IND
	END;

	--Close cursor for Answers
	CLOSE @CurANS ;
	DEALLOCATE @CurANS;

  -- Fetch next record for Question
  FETCH NEXT FROM @CurQSTN 
  INTO @QSTN_NBR,@QSTN_DESC,@ACTV_QSTN_IND,@MNDTR_QSTN_IND,@ANS_JSON
END; 

-- Close cursor for Questions
CLOSE @CurQSTN ;
DEALLOCATE @CurQSTN;

COMMIT TRAN

END
GO

the input parameter you pass .. make it the datatype you are hardcoding

You are going to run into issues once your json payload is longer than nvarchar(max)

You need to talk to your "data architects" to rethink this approach as discussed in this thread

and if you insist on json approach trim down the json to

"q":[
{
"qn": 2,
"qd": "Are you attendig the event ?",
"mndtrQstnrInd": true,
"actvQstnrInd": true,
"answers": [
{
"an": 1,
"ad": "Yes",
"ai": true
},

etc

Also would not recommend using a CURSOR, use one pass batch approach like following

;with src
as
(
SELECT q.QSTN_NBR,
       q.QSTN_DESC,
	   q.ACTV_QSTN_IND,
	   q.MNDTR_QSTN_IND,
	   a.ansNbr,
       a.ansDesc,
       a.actvAnsInd 
FROM OpenJson(@QUES_ANS_DTL,'$.questions')
WITH (
	QSTN_NBR int '$.qstrnNbr',
	QSTN_DESC varchar(200) '$.qstrDesc',
	ACTV_QSTN_IND bit '$.actvQstnrInd',
	MNDTR_QSTN_IND bit '$.mndtrQstnrInd',
	answers nvarchar(max) '$.answers' AS JSON
) as q
cross apply openjson (answers)
with
(
    ansNbr int,
    ansDesc nvarchar(100),
    actvAnsInd bit
) as a
)
INSERT INTO dbo.QSTN(QSTN_NBR,
QSTN_DESC,
ACTV_QSTN_IND,
MNDTR_QSTN_IND,
CRTE_TSTP,
CRTE_USR_ID)

select QSTN_NBR,QSTN_DESC,ACTV_QSTN_IND,
MNDTR_QSTN_IND, getdate(), 'BUBBA'
 from src
where not exists(select 1 
from QSTN tgt 
where tgt.QSTN_NBR = src.QSTN_NBR)

a quick glance (code review) shows some issues

UPDATE dbo.QSTN SET QSTN_DESC = @QSTN_DESC, MNDTR_QSTN_IND = @MNDTR_QSTN_IND, UPDT_TSTP = SYSDATETIME(), UPDT_USR_ID = 'TEST'  WHERE QSTN_NBR=@QSTN_NBR

	IF @@ROWCOUNT = 0
        ELSE

so you are doing an IF ELSE based on @@ROWCOUNT? then the ELSE deals with EVNT_RSVP

Why?