If we leave performance concerns behind we still have the language itself. So, what did I miss in this language? I was thinking of enumerating things that I didn't like but after some thinking I conclude that I just missed object-orientation in general.
But what it means? Let's see... I was not able to use any design pattern (e.g. Factory, Strategy or Decorator) and I needed them very badly! Instead of using design patterns I had to add many ugly ifs.
Another thing I missed was abstraction i.e. I wasn't able to create abstract stored procedure and then implement specialized subprocedures. In T-SQL everything you can do is to add some more ifs :) This language is strongly data-oriented which is maybe good but it's not sufficient IMHO.
Yes - it's very vague but it doesn't make sense to compare stored procedures with OO constructs. T-SQL is just yet another language I've learnt and used after many years of writing Java/C++ code and I just wanted to express my feelings.
I don't know what are perspectives for such SQL languages like T-SQL or PL/SQL but I think they should evolve in an object-oriented way. It would be much easier to write stored procedures/classes in e.g. Java, test them and then deploy on the server. This would be a win-win situation. We would gain performance by having stored code invoked directly on the SQL server while we will still be able to use OO constructs and profit from unit tests, design patterns, etc.
Maybe there already are such languages but I just don't know them (most of them are procedural languages) - if you know some let me know.