CURSOR sample

USE OneSource2015

Go

/*

Some cursor samples.

*/

--1.) declare the cursor

declare cTestTable cursor for

select --<< select the data you want to loop through, important: select only the columns you need.

       test.test_description,

       test.test_id

from

       test_table test

--2.) delcare variables that matches the fields in the select statement.

------this will be populated from the cursor.

declare @test_description varchar(50),

              @test_id int

--3.) open the cursor

open cTestTable

--4.) first do an initial read and fetch values from the cursor into the variable.

fetch next from cTestTable into @test_description, @test_id --(this is the same as moving to a next record and reading the row.)

--5.) Check the status of the cursor to make sure it has not reached the end of the table,

---- and continue with the loop. Fetch in the same order as the columns in the select statement.

while(@@fetch_status=0) --( 0 indicates all is ok)

begin

       print UPPER(@test_description) + ': Calculated id: ' + converT(nvarchar(50),(@test_id*12));

        -- Do any operation before the next fetch to insure it does not duplicate a read or read when the status is error.

       fetch next from cTestTable into @test_description, @test_id --(same as movenext)The variable now contains new data from the table, operations can be done below this.

end

--very important... cursor needs to be closed AND deallocated right after use.

close cTestTable

deallocate cTestTable