Using a sub-query instead of a variable in STIntersection?

[ArnonTsairi]

I'm executing the query bellow to intersect a soil layer with a polygon (a parcel) with no problem.
I would like to replace the @parcel in the select statement with something like a sub-query. I was naive to think that putting a SELECT Shape from another layer would do the trick, but it fails.
Any ideas?
Thanks, Arnon

DECLARE @parcel geometry 
        =geometry::STGeomFromText('POLYGON ((170018 1068876, <more coordinates>))',1);
SELECT Shape.STIntersection(@parcel)
FROM soils
WHERE Shape.STIntersects(@parcel)=1

[VinceAngelo]

The OGC intent was certainly to permit subqueries, but there's still the SRID issue with
which to contend. A Microsoft forum would probably be a better place to find experts on
GEOMETRY behavior in SQL-Server 2008. 

ArcSDE just uses an envelope search as a filter and then uses Esri's libraries for topological
comparison (whether it's GEOMETRY/GEOGRAPHY, ST_GEOMETRY, SDO_GEOMETRY, 
PG_GEOMETRY , SDEBINARY, or SDELOB storage).  Shape library comparisons are done at
the precision specified by the layer coordinate reference (inverse of xyscale).

- V

[ArnonTsairi]

Eventually I abandoned (for now) the subquery idea. Instead I'm using a join with STIntersects in the join criteria. This makes the shapes of both layers available for intersection.
Basic, I guess...