In cursor: proper comparison using variable | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

In cursor: proper comparison using variable

Hi All, I failed to find record when using variable in cursor in WHERE clause: ID is uniqueidentifier field in the table
DECLARE @EncounterID uniqueidentifier
……..
WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger WHERE ID = ‘E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012’ -> this works fine and record found Tried to CAST(@EncounterID as char(40)) but still no luck. I would greatly appreciate any advise hot to make it working. Thank you very much in advance Roman

Can you post the query you have written using cursor and the way you set values for variables.
Thank you for quick response, ranjitjain DECLARE @EncounterID uniqueidentifier
—————————————————————-
DECLARE Encounter_Cursor CURSOR FOR
SELECT dbo.[ClinicalEncounter].ID, ClinicalAct.EffectiveTimeBegun FROM
( dbo.[ClinicalEncounter] INNER JOIN ClinicalAct ON ClinicalEncounter.ID = ClinicalAct.ID )
INNER JOIN ClinicalParticipation ON ClinicalEncounter.ID = ClinicalParticipation.ActID
WHERE RoleID = @PatientIDParam ORDER BY ClinicalAct.EffectiveTimeBegun
————————————————————-
DECLARE ChiefComplaint_Cursor CURSOR FOR
SELECT ClinicalAct.Text
FROM ClinicalEncounter INNER JOIN
ClinicalActRelationship ON ClinicalEncounter.ID = ClinicalActRelationship.SourceActID INNER JOIN
ClinicalObservation ON ClinicalActRelationship.DestinationActID = ClinicalObservation.ID INNER JOIN
ClinicalAct ON ClinicalObservation.ID = ClinicalAct.ID
WHERE (ClinicalEncounter.ID = @EncounterID )
————————————————————————-
OPEN Encounter_Cursor
FETCH NEXT FROM Encounter_Cursor
INTO @EncounterID, @EncounterDate -> can see value in @EncounterID variable
…………
OPEN ChiefComplaint_cursor
FETCH NEXT FROM ChiefComplaint_Cursor INTO @ChiefComplaint
-> no record found
As I said before replacing @EncounterID to ‘E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012’ leads to finding record Thank you for your time Roman

You are declaring a cursor with a variable in the query statement, but at that point you have not yet assigned a value to the variable. When you open the cursor, the value at the point where the cursor was declared will be used – which in this case was a null. It doesn’t matter that you are assigning a value before opening the cursor: the cursor is already initialized at that point.
Also you don’t need nested cursor at all.
Read about derived tables and you can do all in one select without nested cursor
Thanks, Adriaan Just tested your correction, works great. One more question appeared now for me.
I used OPEN ChiefComplaint_cursor
FETCH NEXT FROM ChiefComplaint_Cursor INTO @ChiefComplaint in a loop for all @EncounterID that I got from Encounter_Cursor.
Now I need to put ChiefComplaint_Cursor Declaration inside the loop and deallocate it at the end of iteration. Now sure how efficient it is and asking for advise how this can be solved to not use cursor declaration in a loop. Thanks a lot
Roman
try this: SELECT ClinicalAct.Text
FROM ClinicalEncounter INNER JOIN
ClinicalActRelationship ON ClinicalEncounter.ID = ClinicalActRelationship.SourceActID INNER JOIN
ClinicalObservation ON ClinicalActRelationship.DestinationActID = ClinicalObservation.ID INNER JOIN
ClinicalAct ON ClinicalObservation.ID = ClinicalAct.ID
WHERE ClinicalEncounter.ID IN
(
SELECT dbo.[ClinicalEncounter].ID FROM
dbo.[ClinicalEncounter] INNER JOIN ClinicalAct ON ClinicalEncounter.ID = ClinicalAct.ID
INNER JOIN ClinicalParticipation ON ClinicalEncounter.ID = ClinicalParticipation.ActID
WHERE RoleID = @PatientIDParam
)
]]>