If you're referring to Great Stuff spray foam in a can, I don't think that's what you want to use for soundproofing, because it hardens as it cures. Not to mention that to insure you fill each stud cavity, you'd have to poke a bunch of holes in the sheetrock from the bottom of the wall to the ceiling to insert the spray nozzle.

Most of the time, people will use a 3"-4" hole saw to drill a hole in the sheetrock in each stud cavity near the ceiling on one side of the wall, and then blow insulation into each stud cavity. This is a labor-intense process, because not only do you have to fill dozens of stud cavities, but then you also have to patch the sheetrock and repaint it. But, it should deliver the result you're after.