Assuming that you follow the procedure perfectly, including correct choice of firmware version for your router model and hardware revision, you shouldn't brick it. It doesn't mean that firmware will work flawlessly - it may contain bugs - but it should flash and work as well as it can.
If something goes wrong during the procedure (for example you fail to erase NVRAM correctly) you may encounter some bugs. They can be severe or unimportant, and in some cases may render device unusable. You may be able to fix these by following the procedure again if you're able to start flashing (bugs may prevent it). It's even possible that device won't boot correctly.
The firmware may also contain bugs that break some features or make them hard to use, for example it may lack 5 GHz (802.11n) support or flashing may not work (ie. you won't be able to flash another/original firmware easily).
What happens when flashing goes wrong and you can't start flashing again? Some routers and firmwares feature "emergency flashing" mode. To use this mode you have to connect router directly to a computer with a cable, set specific static IP on the PC, and transmit new firmware using some trivial (and probably not user-friendly) protocol (telnet, TFTP etc.)
If emergency flashing doesn't work, it's sometimes possible to flash firmware directly into memory. Some advanced hardware and knowledge is needed to do this and if you're in this situation, you may want to visit nearby hackerspace and ask for their help.
Some useful info: The Peacock Thread.