The answer *so* depends on your development environment. If I was starting a brand new system or application today, all middle-tier business logic would be in a DLL with a matching .NET DAL (database access layer) and most stored procedures would be fairly simple and used for atomic CRUD operations.
Having said that, I have inherited a suite of applications (a system if you will) with a SQL Server back-end. The "BLL" is implemented poorly and inconsistently between the form-class code and stored procs, leaning heavily toward the procs -- no BLL or DAL existed to start. There was no DLL when I started on this and no business objects whatsoever in sight in any of the ten or so apps. Code repetition everywhere with logic conflicts between the form code and the procs, magic numbers galore, with everything else being convoluted because of three-letter variable names, and so forth. Cursors thrown into the procs wherever it was convenient. Changes to this suite of applications take considerable analysis and testing time because of the scattered nature of the logic, vague SQL column names (with no documentation to start with), procs that are thousands of lines long, things already mentioned, et al. The most important SQL table in the system has at least four different "business objects" stored there, and would greatly benefit all involved if it was normalized and split accordingly. I think you get an adequate picture.
I have slowly started to convert over to a .NET DLL for the middle-tier business logic + XML documentation, simply for the sake of understanding the system first of all (and documenting business-processes), let alone rewriting it. As I am able, I am replicating ALL business logic from the form code and procs into .NET BLL classes, and then deprecating both the form code and procs where I can. I'll take a system I can quickly understand and modify any day over a system that is mostly implemented in SQL, with documentation non-existent, very little consideration for normalization, business-rule conflicts, etc.
I love T-SQL as a beast of burden, but I use it for that which it was designed: set-based data processing, data-rules, data-security, FK/PK constraints, CRUD operations, et all. While I have seen BUSINESS-rules successfully implemented in SQL, it's not the path I would choose for many reasons.
SQL Server user and admin since v2000
"It takes a village to raise a SQL Server."