Open cell spray foam can effectively attenuate sound as part of a structure with additional features.

See this piece from Spray Foam Magazine with research on spray foam in walls. Illustration below is from the article.

Filling an entire conventionally built void with foam will increase the sound transmission. Not to mention that most sound transmission in a building is through flanking pathways, such as HVAC and non-sound attentuated components.

Comprehensive research on sound attenuation assemblies published by the National Research Council of Canada, available here (PDF), notes that:

"Sound-absorbing materials are only effective when used in assemblies with the faces correctly isolated as described above. Using sound-absorbing materials in a rigidly connected assembly is a waste of money. When the two layers forming the wall cavity do not have rigid connections between them, adding sound-absorbing material to the cavity increases the STC by about 10 points." (emphasis added)

This post was edited by worthy on Sun, Sep 22, 13 at 9:54

spray foam is ususally used to create a tight building envelope. exterior walls ( waste of $$ imo) roofline for
unvented attic when ducts/equipment are in attic, attic floor when no ducts or equipment are in attic & under floors in homes raised off the ground.

sound insulation between floors, or in interior walls are usually sound batts, sound board, or double layer of sheetrock with hat channels, the latter two for walls only.

make sure depth (inches) of foam equals what is required
by code and actual depth is achieved. no "2-3" performs like R-30", but actual depth x actual R-value= code requirments.

best of luck.

Closed cell insulation is what we have for the exterior envelope and it is very sound deadening.

However it is also 5X the cost of fiberglass, so we did not use it anywhere else.