I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the value or values of a another table is to be updated. The table or tables that are to be updated may vary depending on the answer of the user and the data type of column might also vary.
I was thinking about using a metadata table, in this example AffectedData, containing information about the tables, the values and data types. Something like this pseudo code:
CREATE TABLE AffectedData ID int TableName nvarchar(50) NOT NULL -- table to update ColumnName nvarchar(50) NOT NULL -- the col to update DataType nvarchar(50) NOT NULL -- the col data type DataValue nvarchar(50) NOT NULL -- the value to update with Operation int --insert or update
When an answer is inserted a trigger or a procedure might query this table and by using some conditional logic cast the DataValue to the correct data type, using DataType as hint, and insert or update into to the table specified in TableName. In other words I assume I have to use dynamic SQL, using sp_executesql to execute the statement.
Is this a way to go or is there some other wise way of achieving this?