There is no need to explain how important logging is. And when you log an error, you want to log it with all relevant information such as parameters, environment settings, etc. The common way to do it is to gather everything into XML and call the logging stored procedure.
Just like that.
The only problem here is that this code has to be maintained - when someone adds a parameter to the stored procedure it also has to be added to the @params, and unfortunately it is not always done.
My colleague and I have developed a way to do it automatically. Now proper logging is done as easy as adding two templated tags in comments like that
How it works.
The main idea is to parse the code of the procedure and inject the code. So we have created a trigger on the database to track changed stored procedures and mark them for further enhancements. I will not provide the code of the trigger as it can be done in lots of different ways (Service broker, a table with a procedure name/id and its status, even direct changes if you are sure that the procedures would not be created - only altered). The only thing to notice here is that it should check which user has committed the changes to avoid permanent re-application of the templates (see below).
After the procedure is marked as changed, we need to call the handler. Again, there is no need to go deep with details of calling it as it can be done differently, but it should be called by a special user, we call it 'imp'
And of course, the templates and the injections should be predefined. We use functions that accept object identifiers (schema_name + object_name) and return the injection as nvarchar. The templates themselves are stored in the table with start/end tags and a function name.
First, we need to check that the templates are placed correctly - so there is no intersection of tags, the tags are closed etc. After that we apply the templates with regular expressions. Here is the main part of the procedure, the whole thing will be attached/posted as comments
Now we only need to create the templates - and we can create them not only for logging but also for nice-to-haves like execution script with all the parameters in the header and who knows what else. So we use 3 templates:
# Header - a commented script to execute a procedure with all the parameters set to NULL
# Parameters - declares default variables like @trancount and @params, sets default options and assembles the xml.
# Checker for missing temporary tables. If the temporary table is not created in the procedure and is missing in the runtime, it will fail anyway. Custom errors allow us to catch it and not to have a compilation error.
Now populate the templates table...
Create test procedure
... apply templates
... and check the results!
PS The code is provided "AS IS" so I take no responsibility for unexpected results
PPS Keep the templates safe - the SQL injection is dangerous :)
