Function to get Lat Long points for geography polygon SQL

Question

I've created a function to return a string of the points within a multi-point geography field in MS-SQL. I was wondering if there is a better way of doing this? What I want to is supply a list of points as a Lat/Long co-ordinates to an external client who does not use SQL geography data types (they are using SQL Lite).

   Create FUNCTION [dbo].GetLatLongForPolygon
(
    @Perimeter  GEOGRAPHY
)
RETURNS Varchar(max)
AS
BEGIN

      declare @NumPoints as Int
  declare @OutputString as varchar(max)
  declare @latpoint as varchar(max)
    declare @longpoint as varchar(max)

 set @NumPoints = @Perimeter.STNumPoints()



 while @NumPoints >0
     begin
        set   @LatPoint = @Perimeter.STPointN(@NumPoints).Lat 
        set @longpoint = @Perimeter.STPointN(@NumPoints).Long 
        set @OutputString = concat (@OutputString, '(' , @latpoint, ',', @longpoint , '), ')
        set @NumPoints = @NumPoints -1
    End

RETURN left (@OutputString, len(@outputstring)-1)

    GO

Answer 1

Yes. Yes there is. I'm using a tally table to eliminate the cursor and a standard idiom to emulate the missing string concatenation aggregate in T-SQL.

declare @g geography = geography::STLineFromText('LINESTRING(20 20, 21 21, 22 22)', 4236);

with cte as (
    select @g.STPointN(n.Number) as [Point]
    from dbadmin.dbo.Numbers as n
    where n.Number <= @g.STNumPoints()
)
select stuff((
    select ', ' + concat('(', Point.Lat, ', ', Point.Long, ')')
    from cte
    for xml path('')
), 1, 2, '')