Reputation: 3
I need help on a cursor in Microsoft SQL Server.
I have two tables. The first one stores data from employees (table PFUNC
), and column Chapa
identifies a person. In the other table, ABATFUN
, I have the registry of the days the employees was at the company.
I was trying to write a cursor that checks for every day if the employee was at the company. The column CHAPA
is the FK in the table ABATFUN
.
In my code, I just limit the cursor to only 2 employees, using the exclusive identifier "chapa".
declare
@startDate DATE = '2020-03-01',
@endDate DATE = '2020-03-31',
@chapa varchar(10),
@name varchar(50)
--declare the cursor. Here i get the registry for only 2 employees for test
declare cur_Chapas cursor for
select CHAPA, NOME
from PFUNC
where CODSITUACAO IN ('A', 'U','X','V')
and (CHAPA = 03304 or CHAPA = 03368)
open cur_Chapas
fetch next from cur_Chapas into @chapa, @name
while @@FETCH_STATUS = 0
begin
while (@StartDate <= @endDate)
begin
if exists (select * from ABATFUN where CHAPA = @chapa and DATA = @startDate)
print 'There are registry of the employer name: '+ @chapa +' '+ @name+ ' Day: ' + convert (varchar,@StartDate,4)
else
print 'There are not any registry of the employer name: '+ @chapa +' '+ @name+ ' Day: ' + convert (varchar,@StartDate,4)
set @startDate = DATEADD(day, 1, @endDate)
end
fetch next from cur_Chapas into @chapa, @name
end
close cur_Chapas
deallocate cur_Chapas
The problem is, when I run this code, it show me data only for the first employee. The cursor doesn't go to the next iteration. What am I doing wrong?
What do I need to do to make the cursor bring me the data from the second employee ?
When I run only the cursor, without second "While" in the code, it brings me the data from "Chapa" and "Name": https://i.sstatic.net/zaqFU.png
Thanks and sorry for my bad English =]
Upvotes: 0
Views: 62
Reputation: 18410
You need to reset @startDate
every time through the outer loop.
while @@FETCH_STATUS = 0
begin
set @startDate DATE = '2020-03-01'
while (@StartDate <= @endDate)
begin
if exists (select * from ABATFUN where CHAPA = @chapa and DATA = @startDate)
print 'There are registry of the employer name: '+ @chapa +' '+ @name+ ' Day: ' + convert (varchar,@StartDate,4)
else
print 'There are not any registry of the employer name: '+ @chapa +' '+ @name+ ' Day: ' + convert (varchar,@StartDate,4)
set @startDate = DATEADD(day, 1, @endDate)
end
fetch next from cur_Chapas into @chapa, @name
end
When you process the second result from the cursor @startDate
is already equal to @endDate
and the inner loop is not run.
Upvotes: 1