When you're dealing with an architecture that has a separate DAL and business logic layer, stored procedures and SQL do not belong in the business logic layer. Strictly speaking, that layer should be agnostic to how the data is stored and retrieved, and should only be responsible for manipulating the instances returned from the DAL, enforcing business rules, and performing validation.
As far as what's practical, though, the lines often blur, as it's often difficult to balance efficiency (how fast the code runs) and expediency (how fast you can produce working code) against ideal design. If you have complex filters as the result of rules in your business layer, it would be extremely inefficient to return everything from your DAL and filter in the business layer, although that would follow the strict separation between layers. And it would take too long and be a maintenance nightmare to write a separate function for every possible way to filter your data. For a large project, the solution is often to introduce a way to build a custom "request" (or "query") class that gets passed to the DAL, which returns results based on the custom object, but this is overkill for a small project (unless you use an ORM such as Entity Framework, which exposes your repository as a collection of IQueryable<T>, which lets you build your query without concern for how it's actually going to be executed against your data store).
