Any app that could enable you to use the phone number associated with your iPad would be borderline legal, at best. That phone number is for the phone company to track your iPad's 3G account, not an activate phone number. As such, activating SMS service on that number without the carrier knowing it would (at the very least) violate your terms of service.
Understand that SMS does not use the same data network/services as your 3G or LTE data plan. It's a service that rides on the standard cellular phone network. That's why it works with just about every cell phone out there, smart or not.
This is why apps that offer SMS service for the iPad require you to have another number. They have to register that number with a carrier so that you can send and receive messages.
Will carriers ever integrate SMS into the data plans? Probably not. As more and more people move to smart phones with data plans, SMS is dying. Sending messages over the internet is cheaper.