Your arguement is certainly valid, but there are some very good reasons to abstract your middle tier from your database. 

First one that jumps out is how poorly TSQL organizes logic.  Since TSQL isn't an object based language, encapsulation and reuse make building a good business layer difficult.  This does change with CLR in the mix for Yukon however.

Second, is the fact that a good middle tier can be ported to other databases and even operating systems.  While often this isn't practical since larger apps quickly become inner-dependant on the SQL engine, keeping your business layer abstracted from your data layer does allow for quite a bit of flexibility when it comes to how you store data.  If an app is well insulated, it can be switched from SQL Server to MySQL relatively quickly.  Tieing yourself to a database vendor is generally not an issue, but for many apps closing yourself off can be closing down opportunities.

Third up is performance.  When logic exists solely in your database, it becomes very difficult to optimize system logic.  For example, rewritting your encryption/compression routines from DES to AES would likely require changing linked DLLs in SQL Server.  If a true middle tier existed, it is likely as simple as changing a couple of methods on the connectivity object.  Again CLR in the database addresses this issue to some degree.

Forth, security.  By creating a middle tier, I can link my client to it via a public port then on an entirely seperate network link my data connection to my SQL Server.  Thus for a hacker to gain access to data they would have to hack either the client/server interface which is usually highly encrypted and quite often proprietary or they would have to attack the middle tier server and then bust through a second layer of security to the data server.  If the business logic were in the SQL Server, my clients would be connecting directly to SQL Server and I would have to worry about threats such as Slammer if my app was internet based.

Last but not least, because it is easier for most companies.  A middle tier written in the same language as your client makes maintaining business logic easier out of the gate.  Rather than having a DBA that does one side and a developer that does another or developers needing to know two programming languages, the database becomes nothing more than a repository of information.  Documenting, source control, etc become simpler because the syntax is uniform.  This can be a pretty lame reason, but since most companies go with what they know and databases usually being a mystery to everyone but a few this just seems natural for these companies.