SQL Code Snippets - Sample Simple Cursor

For those of you, like me, who like to have a piece of working code sitting around that I can easily template off of.  Here is my example for a simple cursor that can easily be manipulated if you absolutely, positively, cannot avoid using a cursor in SQL

Create table #tmp

(

      myid int identity(1,1)

      , avalue varchar(255)

)

insert into #tmp

(

      avalue

)

SELECT

      'test1'

UNION

SELECT 'test2'

UNION

select 'Messag3'

DECLARE @myid int, @message varchar(255)

PRINT '-------- quick print --------';

DECLARE mycursor CURSOR FOR

SELECT  myid from #tmp

OPEN mycursor

FETCH NEXT FROM mycursor

INTO @myid

WHILE @@FETCH_STATUS = 0

BEGIN

    PRINT ' '

    SELECT @message = 'ID PRINT: ' +

        cast(@myid as varchar(255))

      PRINT @message

     

        SELECT @message = (select avalue from #tmp where myid = @myid)

       

        PRINT @message

        -- Get the next id

    FETCH NEXT FROM mycursor

    INTO @myid

END

CLOSE mycursor;

DEALLOCATE mycursor;