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
