Which is better, filtering results at db or application?

Question

A simple question. There are cases when I fetch the data and then process it in my BLL. But I realized that the same processing/filtering can be done in my stored procedure and the filtered results returned to BLL.

Which is better, processing at DB or processing in BLL? And Why?

consider the scenario, I want to check whether a product exists in my db and if it exists add that to the order (Example taken from answer by Nour Sabony below)now i can do this checking at my BLL or I an do this at the stored procedure as well. If I combine things to one procedure, i reduce the whole operation to one db call. Is that better?

Answer 1

well, the fatest answer is at database, but you may consider something like Linq2Sql, I mean to write an expression at the presentation layer, and it will be parsed as Sql Statement at Data Access Layer.

of course there are some situation BLL should get some data from DAL ,process it ,and then return it to DAL. take an example : PutOrder(Order value) procedure , which should check for the availability of the ordered product.

public void PutOrder(Order _order)
{
foreach (OrderDetail _orderDetail in _order.Details)
   {
    int count = dalOrder.GetProductCount(_orderDetail.Product.ProductID);
    if (count == 0)
    throw new Exception (string.Format("Product {0} is not available",_orderDetail.Product.Name));
    }
    dalOrder.PutOrder(_order);
}

but if you are making a Browse view, it is not a good idea (from a performance viewpoint) to bring all the data from Dal and then choose what to display in the Browse view.

may be the following could help:

public List<Product> SearchProduts(Criteria _criteria)
{
string sql = Parser.Parse(_criteria);
///code to pass the sql statement to Database procedure and get the corresponding data.
}