The first question I have is whether your attic is vented or non-vented.
If you are planning to spray between the rafters, you need to be concerned about standing moisture rotting your sheathing, as SteveSaunders mentions in his excellent Q&A on this topic, "I'm converting my attic into a non-vented conditioned space. Should I use open cell or closed cell foam to insulate?"
If you are spraying between the rafters, and if the attic is open, I prefer an open cell insulation, because the water doesn't get trapped in it. You will need to check with the local codes, however, because open cell insulation is not always allowed.
Closed cell insulation is useful if you want a vapor barrier, and it has a higher R-value. It is very important not to trap moisture, however, as your structure can very quickly be compromised.
The best thing about foam insulation is that it stops infiltration. Even the finest insulation is not doing its job if the air can get around it, which is often the case with poorly installed insulation of any kind. Was that the reason it was recommended in your case?
For more information:
Also, check Alex Georgiou's Q&A "I want to add wall insulation to my 1920 home. I have two locally available options -- Icynene and Tri-polymer foam. Can you advise me?"