I need some advice...

In most of my applications, I find myself making many SPROCs that return the same data only with different criteria. For example, I will have a combination of the following: usp_SelectDealerByDealerID, usp_SelectActiveDealers, usp_SelectAllDealers, etc... These SPROCS return the same result set structure but different data.

I am tired of maintaining several SPROCs and think there has to be a better way of doing it.

Here is what I started doing: I created one usp_SelectDealer SPROC, and I am including several optional parameters and I can achieve my desired results using any combination of parameters. I create one function in my DAL to accept all parameters and retrieve the data, and I am having my BIZ layer contain functions like GetAllDealers, GetActiveDealers, etc. and be responsible for the parameters to supply. I think this is working well because I have a limited number of SPROCS, but I am worried I am going to build a monster that is hard to maintain.

Another idea I had was to have one general SPROC (usp_SelectDealers) that would accept a variable number of DealerID's and return those records. I would have to have another query that returned the dealer ID's of all active dealers (for example), and then provide those to the main SPROC.

How does everybody else deal with issues like these? Am I overthinking this?