It can be used to great effect. This old song serves as a great example of vocal fry and also a great lesson on caring about it/getting aggravated by it: https://www.youtube.com/watch?v=7hx4gdlfamo
Try singing it like he does, fry and all. It's fun and sounds pretty good.