Cage length is about the range of gears, not sprocket size per se.
You need a chain that's long enough (just) to change onto the big/big gear, otherwise you may break something if you ever select that gear by mistake.
The small/small gear would require a much shorter chain if the derailleur cage wasn't there to take up the slack. The long cage/short cage bit is about how much slack chain the mech can cope with. If the cage is too short, the chain will hang slack in small/small, and will be prone to coming off the chainring.
A standard road setup of 53/39 & 12-25 requires the cage to wrap 53+25 - 39+12 = 27T
A bike with a touring triple setup may have 48/38/24 & 12-32 requiring a capacity of 48+32 - 24+12 = 44T
Typical short cage mechs have a capacity of 28-29T, long cage of 39-40T
The other issue is maximum sprocket size. This is dictated by the slant angle on the parallelogram and how far the top jockey wheel is from the axle in the bottom gear position.
Generally road rear mechs will cope with a sprocket up to 29T, possibly 30. If you want a bigger sprocket, you need an MTB rear mech. If you use one of these with a road cassette, the changes in the larger sprockets will be less snappy as the jockey is further from the sprockets than is ideal.