Techniques for cleaner dynamic SQL generation

Very often when working in databases we have to resort to dynamic SQL generation to solve a problem. For example, common instances of this would be when performing an action across a number of different tables, or performing a PIVOT on a data set where the set of pivot results is not pre-determined. This latter example is one which I’ll take through this walk through.

The scenario is that we have a table of Person data and a table of Payslip data, and the requirement is to return each Person with the salaries listed out year on year. Its a simple example, but not unlike a real-world problem.

Continue reading Techniques for cleaner dynamic SQL generation