--sample assumes two tables - reference.Townland and bioenergy.soils
--create a sample area by buffering a point
DECLARE @g AS geometry;
    SET @g = geometry::STGeomFromText('POINT (200000 200000)', 29902).STBuffer(1000);

--create a temporary table variable to store results
DECLARE @results TABLE (GEOM Geometry);

 --select all the polygons in our sample area and add them to the same table
INSERT INTO @results
    SELECT GEOM29902
    FROM reference.Townland
    WHERE GEOM29902.STIntersects(@g) = 1

 --select all the points in our sample area and add them to the table
 --in this case the centroids of a second polygon dataset are used
INSERT INTO @results
    SELECT GEOM29902.STCentroid()
    FROM  bioenergy.soils
    WHERE GEOM29902.STIntersects(@g) = 1

--select all the results in our temporary table
SELECT GEOM
    FROM @results