Eh, there would be a lot of problems associated with a weapon like that. For one, it would probably need to be quite firmly mounted somewhere rather than being any kind of portable. Not only would a weapon (and, it sounds like, the projectile) be very heavy to lug around unless it was based on some kind of arcane propellant, you'd also run into problems like the good old "the harder you push something, the harder that thing pushes back"; in other words, the recoil of something firing a huge projectile long distances with high force would probably send the wielder flying in the opposite direction (or squish them into the ground if they aimed at an upward angle).
Another reason that it probably wouldn't be a thing, even in a stationary version, would be that it would only really work as a support weapon in the context of Bloodborne. Keep in mind that creatures in Bloodborne can regenerate from pretty much anything as long as they have the lifeforce to do so; they can be cut to ribbons, have organs or pieces of brain yanked out of them and worse, and just get right back up and fight on. Shooting a beast with a giant stake might temporarily cripple it or, if you're lucky, pin it to the ground, but it'd be pretty unlikely to actually kill it in one shot. Then you would need to have Hunters move in on the immobilized beast or keep multiple giant stakes lying around to sustain fire and inflict enough damage for it to actually outpace the beast's regeneration, with reloading of the mechanism with each shot... I imagine it'd be wildly impractical.

So I guess my conclusion is that, if it was mounted somewhere with unusually good sight-lines across Yharnam, was incredibly accurate and was used as a means to disable large targets for Hunters on foot to finish off, it might have some merit. Which would make it something definitely [I]not[/I] suited for the Fire Dancers, who can't exactly be too stationary or they'd be taken out by the Healing Church.