I have Bcd996p2, scanner p25 digital 800 looking at buying a terre wave antenna on scanner master looking for what kind of coax I should buy with BNC connector..
The exact coaxial cable you need will depend on a coupe of factors. Without knowing those, it's impossible to recommend a suitable cable.
All coaxial cable has some signal loss. The amount of loss will increase with frequency and cable length.
The cable run length is important to know.
Shorter cable runs may achieve acceptable performance with a less expensive cable.
Longer cable runs may require higher grade cables.
As for the final connection to your radio, it'll depend on what the main cable run is. If it's short and you can get away with smaller cable, you can directly connect to your radio. If your run is long and needs bigger/higher grade cable, then you really do need to use a short "whip" of small cable to take stress of the radios antenna connection.
Pre-terminated cables are available. While it's not impossible for someone relatively new to the hobby to make their own cables, it does require specific tools and skill. Sometimes the frustration/hair pulling can be eliminated by purchasing cable with the ends already installed. It'll cost a bit more, but it can be well worth it if you don't plan on making a lot of your own cables.