STNumRings: Counting number of polygon rings

 USE [GISDB] -- Change this to your database
 GO
 -- Function: STNumRings
 --
 CREATE FUNCTION dbo.STNumRings(@p_geometry geometry)
 RETURNS INT
 AS
 BEGIN
   DECLARE
     @v_GeometryType VARCHAR(1000),
     @v_geom         geometry,
     @v_geomn        INT = 0,
     @v_ringn        INT = 0;
   BEGIN
     IF ( @p_geometry IS NULL )
        RETURN 0;
     SET @v_GeometryType = @p_geometry.STGeometryType();
     IF ( @v_GeometryType IN ('Point','MultiPoint','LineString','MultiLineString' ) )
        RETURN 0;
     IF ( @v_GeometryType IN ('Polygon','CurvePolygon') )
     BEGIN
       SET @v_ringn = 1 + @p_geometry.STNumInteriorRing();
     END;
     IF ( @v_GeometryType = 'MultiPolygon' )
     BEGIN
       SET @v_geomn  = 1;
       WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
       BEGIN
         SET @v_geom  = @p_geometry.STGeometryN(@v_geomn);
         SET @v_ringn = @v_ringn + 1 + @v_geom.STNumInteriorRing();
         SET @v_geomn = @v_geomn + 1;
       END;
     END;
     IF ( @v_GeometryType = 'GeometryCollection' )
     BEGIN
       SET @v_geomn  = 1;
       WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
       BEGIN
          SET @v_ringn = @v_ringn + dbo.STNumRings(@p_geometry.STGeometryN(@v_geomn));
          SET @v_geomn = @v_geomn + 1;
       END;
     END;
     RETURN @v_ringn;
   END;
 END
 GO

 SELECT 'Point' AS gtype, dbo.STNumRings(geometry::STGeomFromText('POINT(0 0)',0)) AS numRings
 UNION ALL
 SELECT 'MultiPoint' AS gtype, dbo.STNumRings(geometry::STGeomFromText('MULTIPOINT((0 0),(20 0))',0)) AS numRings
 UNION ALL
 SELECT 'LineString' AS gtype, dbo.STNumRings(geometry::STGeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)',0)) AS numRings
 UNION ALL
 SELECT 'MultiLineString' AS gtype, dbo.STNumRings(geometry::STGeomFromText('MULTILINESTRING((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) AS numRings
 UNION ALL
 SELECT 'Polygon Outer' AS gtype, dbo.STNumRings(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))',0)) AS numRings
 UNION ALL
 SELECT 'Polygon' AS gtype, dbo.STNumRings(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) AS numRings
 UNION ALL
 SELECT 'MultiPolygon' AS gtype, dbo.STNumRings(geometry::STGeomFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0)) AS numRings
 UNION ALL
 SELECT 'CurvePolygon' AS gtype, dbo.STNumRings(geometry::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)))',0)) AS numRings
 UNION ALL
 SELECT 'GeometryCollection' AS gtype, dbo.STNumRings(geometry::STGeomFromText('GEOMETRYCOLLECTION(CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))), COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0)) AS numRings;