SQLize Online / PHPize Online  /  SQLtest Online

-- create a sample table CREATE TABLE Locations (LocationCode int, LocationPolygon geometry); -- insert some sample data INSERT Locations VALUES (1, geometry::STPolyFromText('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))', 10)), (2, geometry::STPolyFromText('POLYGON((10 10, 20 10, 20 20, 10 20, 10 10))', 10)); GO -- create the function CREATE FUNCTION dbo.GetPoints() RETURNS @ret TABLE (LocationCode INT, PointX INT, PointY INT) AS BEGIN DECLARE @max INT SET @max = (SELECT MAX(LocationPolygon.STNumPoints()) FROM Locations) ;WITH Sequence(Number) AS ( SELECT 1 AS Number UNION ALL SELECT Number + 1 FROM Sequence WHERE Number < @max ) INSERT INTO @ret SELECT l.LocationCode ,l.LocationPolygon.STPointN(nums.number).STX AS PointX ,l.LocationPolygon.STPointN(nums.number).STY AS PointY FROM Locations l, Sequence nums WHERE nums.number <= l.LocationPolygon.STNumPoints() RETURN END;