• 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)