from a pure technical POV I second Kevin and third Craig on the aspect that building crosstab cursors should be delegated to the database engine and that probably the best way to avoid performance bottleneck is to use the DB dialect and specific commands found in that DB vendor implementation.

As you mention "generic" twice: I have no idea how much of such code would be repeated and could/should be handled different to keep the programmers code redundancy at minimum.

Leaving technical area, from a business POV I would think hard on the option to create a structure to create/mainain those SPROCs programmatically or at least have a table or OOP way of structuring/comparing them. Otherwise you are

a) totally dependant of your customer accepting SQL server and duplicating his data there if he uses another backend and linking those servers is not possible.
b) cannot refactor out those redundant elements/string patterns found often across many SPROCs to the creating script, leading to non-DRY maintainance pattern

I am all for using each vendors syntax to speed up the SPROC execution, but you should have a [cross DB-]generic, less performant fallback you can upgrade/specialze to vendor syntax on performance issues.


>In our payroll application there is a large number of possible money and time categories. We keep a list of all those categories in a table. There is another group of tables for which we want to store detail columns (which are a subset of the categories described above) in a cross-tab arrangement. We want to write generic code to build the cross-tab cursors and to update the corresponding base tables when required.
>
>I think the best way to accomplish this reasonably quickly is to keep selected cursors small and to use stored procedures so that all processing happens in the database server. Since the generic code required with be complex, I don't think the stored procedures should be written in Transact-SQL but in C# using CLR + SqlServer integration.
>
>Since I am new to these tools I would like to ask you and the other experienced .NET developers here: Do you think the C# / SqlServer combination is capable of doing the described job well? Also, do you know if someone has already written this code or something similar?
>
>Thank you very much,
>
>Alex
>
>
>
>
>
>
>>It may make sense to develop a rules engine. I worked once for a company that made medical coding systems. They relegated all the decision stuff to a rules engine. It had to be updated every three months, but because all the rules were encapsulated in a single DLL, it made the updates easier.
>>
>>>Hi Thierry,
>>>
>>>I appreciate your and others' recommendation against a rewrite. I fear the project myself. Our motivation for a rewrite is the long term lack of VFP developers.
>>>
>>>The problem our app solves is calculating payrolls according to the laws of Panama, which is a complex set of rules that periodically change... to become more complex. The need to solve this business problem is practically eternal, so the useful life of the rewritten program is potentially very long, assuming we keep it current, and we feel it will pay off.
>>>
>>>We know the business problem well, better that our competitors, and the smallness of the market makes it unattractive to foreign software companies. We believe we have a few years to complete the rewrite. During that time we will cover our flanks by developing a few add on options with West Wind Connection, mostly report oriented with some data capture. Right now our app is 100% LAN.
>>>
>>>We need to make good choices, so I'll be asking many questions:
>>>
>>>We'd like to make the rewrite fairly data driven, so the first question is: Does .NET allow the equivalent of VFP's EVALUATE(lcCommand) ?
>>>
>>>Thanks for the concern and response.
>>>
>>>Alex