CODEWITHSUNDEEP
sql-servert-sql
Detlef7able
Detlef7able

Reputation: 301

Why is the cursor not terminating?

I have a cursor here and don't know why it is not terminating. I am a beginner but I wrote some cursors before and can not explain me the problem.

create procedure testeinsacht
    @mitid varchar(3),
    @kw int
as
    declare cstesteinsacht cursor for
         select AufDat, KunName, KunOrt, KunPLZ, KunStrasse
         from auftrag a
         inner join kunde k on a.KunNr = k.KunNr
         where MitID is not null and dauer is null and MitID = @mitid
         order by AufDat

    open cstesteinsacht 

    declare @adat date 
    declare @kname varchar(30) 
    declare @kort varchar(20) 
    declare @kplz varchar(5) 
    declare @kstrasse varchar(50) 

    fetch next from cstesteinsacht into @adat, @kname, @kort, @kplz, @kstrasse

    while (@@FETCH_STATUS = 0) 
    begin
        if (dbo.weekcalc(@adat) = @kw) 
        begin 
            print cast(@adat as varchar(20)) + @kname 

            fetch next from cstesteinsacht into @adat, @kname, @kort, @kplz, @kstrasse 
        end
    end

    close cstesteinsacht
    deallocate cstesteinsacht

I hope someone can explain me the mistake.

Thanks in advance!

Upvotes: 0

Views: 106

Answers (1)

Attie Wagner
Attie Wagner

Reputation: 1362

As per my comments, change your code to include an else statement as can be seen below:

create procedure testeinsacht
    @mitid varchar(3),
    @kw int
as
    declare cstesteinsacht cursor for
         select AufDat, KunName, KunOrt, KunPLZ, KunStrasse
         from auftrag a
         inner join kunde k on a.KunNr = k.KunNr
         where MitID is not null and dauer is null and MitID = @mitid
         order by AufDat

    open cstesteinsacht 

    declare @adat date 
    declare @kname varchar(30) 
    declare @kort varchar(20) 
    declare @kplz varchar(5) 
    declare @kstrasse varchar(50) 

    fetch next from cstesteinsacht into @adat, @kname, @kort, @kplz, @kstrasse

    while (@@FETCH_STATUS = 0) 
    begin
        if (dbo.weekcalc(@adat) = @kw) 
            begin 
                print cast(@adat as varchar(20)) + @kname 

                fetch next from cstesteinsacht into @adat, @kname, @kort, @kplz, @kstrasse 
            end
        else
            begin
                print 'Value was not True...' 

                fetch next from cstesteinsacht into @adat, @kname, @kort, @kplz, @kstrasse 
            end
    end

    close cstesteinsacht
    deallocate cstesteinsacht

Hope this helps.

Upvotes: 1

Related Questions