The concept of n-tier design is mainly one of flexibility. A proper n-tier design should
allow you to change either the front-end, business logic or back end (database) with no
(or minimal) changing of the other layers. This means that the user interface (front end)
is one layer (local exe, web page, etc). This communicates with the business logic layer
(a set of dlls usually). The business layer then communicates with the database (back
end). Sometimes another layer is inserted between the business logic and back end to
translate for a specific database. The front end never directly comunicates with the data
so it doesn't care what database you use. The business logic layer should use some generic
API (like ADO/ODBC) so that it (for the most part) doesn't care what database that you use
or what front end you use. The database should only store data so it doesn't care how the
data was entered or what logic it went through before being stored.
While this gives a lot of flexibilty you can sometime sacrifice performance as many back
ends offer performance gains by integrating more tightly with it. This is what stored
procedures are: business logic that is embedded in the data layer. You are basically
trading n-tier for 2-tier to acheive a (often slight) performance gain and locking
yourself inst a specific data source. In this 2-tier model changing your data source
forces a change (re-write) of the business logic layer (which actually no longer exists).
Sorry to be so long winded.
I would use stored procedures if the performance gain was significant or if it was
demanded by the customer but not otherwise.