One of the most difficult kinds of software I have worked on is where you use data to define or logic. One kind of program you could write uses meta-data stored in a relational database to write and execute SQL code based on what it finds.
I once created a stored procedure that would identify the structure of a table from Information_Schema.Columns in an SQL Server database, and use the results to create and perform a pivot query. The procedure would query a specified table, identify all the possible options, create a pivot table, populate the pivot table, and then execute a query of the original table joined with my new pivot table to produce dynamic output. If you are familiar with the crosstab query in MS Access, it emulated that capability.
I found it easy to perform all this work manually. But, when I tried to turn it into a procedure that would perform all the individual queries and translate the results into code that could be executed it was rather mind twisting. Nesting strings in strings was probably the most complicated part of the code because in order to include the ‘ character in a query it has to be escaped with a precedding ‘ like ‘’. When you start embedding code that writes code that writes code the nesting/escaping can get confusing. I sometimes had code like ‘’’’’’’’ because of the number of layers of nesting code within code.
Evan so I find it often convenient to write code that writes code, even if it isn’t executed. For example, I like to use queries that parse through lookup tables and create enums for software, thus keeping the database and external software enums synchronized.
Do you write software that writes or executes software? If so, get into the conversation by sharing your experience here.
Cheers,
Ben