I have recently joined a VFP project in progress. It is a very large VFP project using SQL Server 2008R2 for all data storage. It is an accounting system and has about a dozen separate databases, each storing the data for one accounting entity. There is one central DB that holds data that can be used for any of the companies such as defaults, look-ups, etc. I am doing a bunch of reports and decided to create Stored Procedures that I can pass parameters to, to provide the data for each report.

I originally thought I could put all of the SP's in the central DB but I was unable to figure a way to pass the needed DB name and query that DB when doing it this way. I then decided to create the SP's in each DB but this seemed like a maintenance nightmare since there are currently 12 DB's and could be more going forward. I finally decided to create report programs that would create the stored procedures on the fly as needed when doing the reports. This is pretty quick and also has the SP advantages. So far it is working well but I am having second thoughts that this is the best way to do this. I just want to make sure I am doing what is best for the project.

Any thoughts?

Thanks,
Bill