I am populating a CURSOR with 2 fields namely ErnCode & ErnName from a temporary table using a stored procedure. Suppose 2 ErnCodes - PF & PT are present in this cursor. Next I want to retrieve only those records from a table where the ErnCode is the same as the ErnCode in the cursor i.e. only PF & PT records should be retrieved from the table since PF & PT are the only 2 ErnCodes present in the cursor. This is the stored procedure I have framed:
CREATE PROCEDURE YourPay
DECLARE ErnCodeCursor CURSOR FOR SELECT ErnCode,ErnName FROM #TempTable
Now when I execute this stored procedure in the Query Analyzer, only the PF records get retrieved but not the PT records. Why so? Shouldn't the FETCH NEXT line go to the next record in the cursor (which is PT in this case) & then the stored procedure retrieve the PT records? Where am I going wrong? What do I do to ensure that after the PF records have been retrieved, the PT records also get retrieved & displayed as the cursor contains, apart from the ErnCode PF, the ErnCode PT also?