Thank you very much for the article! I found it very useful. I do have a comment though. With the approach presented, we have to incorporate logging code within each of the individual stored procedures thus making them very bloated. In addition, pretty much all of the logging logic will be redundant and spread all over the place, so if we decide to change something about our logging process, it would be very problematic. Wouldn't it be better to have a dedicated stored procedure, which does the logging work and accepts a bunch of parameters? This way everything is more centralized.
Exactly my thoughts on this.
I guess this is why I'm against moving my SQL to SP. I run them now with a bat file and I direct the output to the same file name in the same folder only with .log at the end. I get the same type of information without the clutter.
Thank you for that input. Others are probably wondering the same thing.
Suppose you create a SP to call when you need to do the logging INSERT. The minimum number of parameters to that SP is going to be all of the columns in the INSERT statement. You may want to include a couple of additional control parameters, and maybe some OUTPUT parameters as well.
Now let's assume that something about your logging changes. Perhaps you add a column to the ProcessLogs table. You'll still need to seek out all of the EXEC statements for your SP and modify them to include the additional columns. Sounds to me like it is still a lot of work. Additionally you may still have to add the code to put whatever into those columns as appropriate to the batch process.
At least, the way the INSERT statements explicitly reference all of the table's columns, none of them will fail as long as you add columns that support NULLs.
It is possible (although I can't say for certain), that any change to a logging process, will involve changes wherever you've referenced the SP.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]