Agreed about the lack of ORDER BY.
However, luck would have it that it returns data for me, as long as I run it in a database that returns data for this:
SELECT
TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]
FROM
INFORMATION_SCHEMA.routines r
INNER JOIN INFORMATION_SCHEMA.routines rdep
ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'
AND r.ROUTINE_NAME != rdep.ROUTINE_NAME
Now, although the lack of ORDER BY means you can't rely on the output, I would hope that the two executions of the CTE happening together in milliseconds would return the same 10 rows, after all the order they are returned in is not randomised, it is just not user predictable...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);