For a multiline intersecting several polygons, get the respective lengths

I have one table containing polygons and another containing multilines. For a given multiline, I can get just the intersecting polygons, like so:

SELECT p.geo, p.id
FROM polygons p, lines l
WHERE p.geo.STIntersects(l.geo) = 1 AND l.id = @lineID

To开发者_Go百科 also get the line itself, I add:

UNION ALL
SELECT l.geo, l.id
FROM lines l
WHERE l.id = @lineID

How, though, do I get the respective length of the portion of the line that intersects the given polygon, as a third column?

As simple as it should be, really.

SELECT p.geo, p.id, p.geo.STIntersection(l.geo).STLength()
FROM polygons p, lines l
WHERE p.geo.STIntersects(l.geo) = 1 AND l.id = @lineID