It's my first time with looping through data in a query. I know there are two ways of doing this and one is using CURSOR (I read it'snot the nicest thing to use). Wouldn't mind hearing your thoughts.
I will copy and paste my first attempt at usingCURSOR and FETCH. I need to loop through the results from a select statement and put them into a temp table called tblMain. Here it goes:
--keep in mind #tblMain has already been created... --declaring my looper DECLARE ACREmpsCursor CURSOR SCROLL FOR --for the following query SELECT DISTINCT date_time, ssn, emplid, employee_name, division, payroll_sect FROM tblRecords a WHERE ((Finalized = 0) or (Finalized = 1 and date_time = currentPayrollDate )) ORDER BY Division, payroll_sect, employee_name -- I need to store above results into #tblMain, not sure how this -- part works... -- does ACREmpsCursor hold the result set from the above query? OPEN ACREmpsCursor; FETCH NEXT FROM ACREmpsCursor; -- while there is a record, do the stmts between begin and end..? WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #tblMain(emplid, name, division, payroll_sect) VALUES(emplid, employee_name, division, payroll_sect) END -- doesn't quite work...
Here's the error message:
"Msg 128, Level 15, State 1, Procedure getACRSummaryEmployeesDuplicate, Line 52
The name 'emplid' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
So it has a problem with 'emplid'. Am I to use variables like "select @tempEmplid = emplid ... from tblScanRecord..." ?