The problem is that putting code into your database has all the problems of data migration with no testability. It's really hard to write tests and debug code compared to a proper language.
Automated tests of T-SQL stored procedures are also possible. There are couple of ways you could do that, but in our case we just write “unit” tests in C# which call the procedures. This integrates nicely into Visual Studio’s Test Explorer, is debuggable etc. You can even step through a C# test, and then just “step into” T-SQL being called from there!
There are valid reasons for not putting business logic into stored procedures, but “not being a proper language” is probably not one of them, at least in the case of T-SQL. Then again, there are also reasons for using stored procedures. Everything is a tradeoff…