CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012
Michael Cherevko
Michael Cherevko

Reputation: 295

Using fetch next with where in cursor

Is there any option to search inside cursor?
For example: I have a table(MyTable) with row number and value,
that I want to copy to another table(TestTable),
but let's say that if there was a value >= 5 then the next value,
that I want to copy should be <= 3.
I can use something like this:

create table TestTable
(row tinyint,
value tinyint)

declare @row tinyint, @value tinyint, @trigger bit
declare test_cursor cursor fast_forward for
select row,value from MyTable order by row
open test_cursor
fetch next from test_cursor into @row,@value
set @trigger = 0
while @@FETCH_STATUS = 0
    begin
        if @trigger = 0
            begin
                insert into TestTable values (@row,@value)
                if @value >= 5 set @trigger = 1
            end
        else if @value <= 3 
            begin
                insert into TestTable values (@row,@value)
                set @trigger = 0
            end
        fetch next from test_cursor into @row,@value
    end
close test_cursor
deallocate test_cursor

That will work, but my question is: is there an any way to search inside cursor
for the next falue that <= 3 once trigger = 1,
instead of fetching next row over and over every time?

Upvotes: 0

Views: 20949

Answers (1)

Rikalous
Rikalous

Reputation: 4564

No, cursors don't support the kind of querying that you're after. You will have to visit each value and check it in the loop.

Upvotes: 2

Related Questions