Cursoring CTE : Using Cursor with CTE

When I tried to create cursor with the result of CTE, I was greated with the following error:

Incorrect syntax near the keyword 'declare'.

My piece of code was like below:

with myCTE(objId, name) as

(

      select top 5 object_id, name from sys.columns

)

declare myCur cursor for

Select objId, name From myCTE

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'declare'.

This means my expected syntax was incorrect, after a few minute of google I found in one of the blog that  the declare cursor statement needs to be on the top of CTE declarations. So, here’s the correct syntax to define cursor with CTEs:

declare @objId int

declare @name varchar(10)

declare myCur cursor for

with myCTE(objId, name) as

(

      select top 4 object_id, name from sys.columns

)

Select objId, name From myCTE

Open myCur

Fetch next from myCur into @objId, @name

while @@fetch_status <> -1

begin

    print @objId

      print @name

      Fetch next from myCur into @objId, @name

end

close myCur

deallocate myCur

------------OUTPUT---------

117575457

xserver_na

117575457

xdttm_ins

117575457

xdttm_last

117575457

xfallback_

cool finicky learn… 

Post Reference: Vikram Aristocratic Elfin Share