Forums - C# Corner

Forum guidelines
Samkelo Ngubo

Samkelo Ngubo

  • 1.6k
  • 25
  • 99

how can I convert a cursor to an alternative faster query

Apr 16 2018 7:04 AM
DECLARE @KeyAccountID UNIQUEIDENTIFIER = '7562F080-52FF-49EB-A4F3-2749B049155D' --SASOL
DECLARE @fromDate Date = '2016-11-01'
DECLARE @toDate Date = '2016-11-30'
BEGIN
DECLARE cur_prog CURSOR FOR
SELECT CONVERT(NVARCHAR(MAX),QD.Data)
FROM Question Q
JOIN QuestionData QD ON Q.ID = QD.QuestionID AND QD.DataType = 'ProgrammeInfo'
JOIN QuestionnairePage QP
JOIN Questionnaire Q2 ON Q2.ID = QP.QuestionnaireID
JOIN QuestionnairePageDependency QPD
JOIN Dependency D
JOIN RetailGroup RG
JOIN KeyAccount KA ON RG.ID = KA.RetailGroupID
ON RG.ID = CAST(D.Answer AS UNIQUEIDENTIFIER)
ON D.ID = QPD.DependencyID AND DependencyTypeID = '2BA42522-5484-4048-94F4-DDA3F2FDDB74'
ON QPD.QuestionnairePageID = QP.ID
ON QP.ID = Q.QuestionnairePageID
WHERE QuestionTypeID = '6EACB5DB-C1BE-4C71-82A6-0E8DBFB4FCAF' AND KA.ID = @KeyAccountID
UNION
SELECT CONVERT(NVARCHAR(MAX),QD.Data)
FROM Question Q
JOIN QuestionData QD ON Q.ID = QD.QuestionID AND QD.DataType = 'ProgrammeInfo'
JOIN QuestionnairePage QP
JOIN Questionnaire Q2 ON Q2.ID = QP.QuestionnaireID
JOIN QuestionnairePageDependency QPD
JOIN Dependency D
ON D.ID = QPD.DependencyID
ON QPD.QuestionnairePageID = QP.ID
ON QP.ID = Q.QuestionnairePageID
WHERE Q.QuestionTypeID = '6EACB5DB-C1BE-4C71-82A6-0E8DBFB4FCAF'
AND @KeyAccountID = Cast(Cast(0 AS BINARY) AS UNIQUEIDENTIFIER)
AND D.ID = '76C6AD06-32EE-47D4-8458-7F39B2BD0923'
DECLARE @json VARCHAR(MAX)
DECLARE @tblProgrammes table(ID UNIQUEIDENTIFIER, Name VARCHAR(255), dtStart DATETIME2(7), dtEnd DATETIME2(7))
OPEN cur_prog
FETCH NEXT FROM cur_prog INTO @json
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblProgrammes
SELECT *
FROM
(
SELECT (SELECT StringValue FROM parseJSON(@json) WHERE NAME = 'QuestionDataID') AS QuestionDataID, NAME AS StringName, StringValue
FROM parseJSON(@json) WHERE NAME IN ('dtStart','dtEnd','Name')
) AS src
PIVOT
(
MAX(StringValue)
FOR StringName IN ([Name], [dtStart], [dtEnd])
) AS piv;
FETCH NEXT FROM cur_prog INTO @json
END
CLOSE cur_prog
DEALLOCATE cur_Prog
SELECT CAST(QD.QuestionID AS NVARCHAR(50)) + '_' + CAST(P.ID AS NVARCHAR(50)) AS ID, P.Name
FROM @tblProgrammes P
JOIN QuestionData QD ON QD.ID = P.ID
WHERE P.dtStart <= @toDate AND P.dtEnd >=@fromDate
END

Answers (2)