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