"Premature optimization is the root of all evil (most of it, anyway) in computer programming" - Donald Knuth
The database is exactly that; the data layer of your application. Its job is to provide your application with the data asked for, and store the data given to it. Your application is the place to put code that actually works with the data; displaying it, validating it, etc.
While the sentiment in the title line is admirable, and accurate to a point (the nitty-gritty of filtering, projecting, grouping etc should in the overwhelming number of cases be left to the DB), a definition of "well" might be in order. The tasks that SQL Server can execute with a high level of performance are many, but the tasks that you can demonstrate that SQL Server does correctly in an isolated, repeatable manner are very few. SQL Management Studio is a great database IDE (especially given the other options I've worked with like TOAD), but it has its limitations, first among them being that pretty much anything you use it to do (or any procedural code you execute in the DB underneath) is by definition a "side effect" (altering state lying outside the domain of your process's memory space). In addition, procedural code within SQL Server is only just now, with the latest IDEs and tools, able to be measured the way managed code can using coverage metrics and path analysis (so you can demonstrate that this particular if statement is encountered by tests X, Y, and Z, and test X is designed to make the condition true and execute that half while Y and Z execute the "else". That, in turn, assumes you have a test that can set the database up with a particular starting state, execute the database procedural code through some action, and assert the expected results.
All of this is much more difficult and involved than the solution provided by most data access layers; assume the data layer (and, for that matter, the DAL) know how to do their job when given the correct input, and then test that your code provides correct input. By keeping procedural code like SPs and triggers out of the DB and instead doing those types of things in application code, said application code is much easier to exercise.