bucketlist50 (12/9/2016)
Declare @Counter int = 1;
with cte as (
SELECT @Counter as c,ASSET_NUM,[SHAPE].STPointN(@Counter).STAsText() as Points
FROM [dbo].[GEOMETRY_TABLE]
where ASSET_NUM = 9782654
union all
SELECT @Counter+1,t.ASSET_NUM,t.[SHAPE].STPointN(@Counter+1).STAsText()
FROM cte inner join [dbo].[GEOMETRY_TABLE] t
on cte.ASSET_NUM = t.ASSET_NUM and t.ASSET_NUM= 9782654
where @Counter < 5
)
Select * from cte option (maxrecursion 0)
You aren't changing the value of the counter. You are doing a static expression of 1+1. I don't know how your output is incrementing column C, but you are definitely not assigning a new value to the @Counter expression.
Until you get your statement working with 5 points, you should probably not include "maxrecursion 0". That is why your get an infinite loop rather than a loop maxing at 100. Maxrecursion 0 can cause server performance and stability (e.g. crash) problems.
Wes
(A solid design is always preferable to a creative workaround)