Not sure I'll be any help, but in case it stimulates some thought here's what we do.
Our Logging sproc is called by every sproc we have ever written (give or take a couple ...) it creates a log record for the Sproc being called, and its parameters, and then Updates that log-row on exit with the success/fail value. The log row is created with a special never-otherwise-occurring outcome-value to indicate "still running" or "terminated without returning a value", and that gets overwritten by the actual return-value when (if!!) the Sproc successfully calls UpdateLogRecord
I used to concatenate the parameters with a "~" separator, thinking that saving CPU was key for this job, but it meant whenever I wanted to review WHAT the parameters of a call were I had to open the source of the Sproc and figure out what the concatenation order was. And of course that was liable to change, over time, so on a Client's database, that hadn't been updated for a while, it was a guess as to what the parameter order was.
Brett (x002548) .. RIP ... told me not to worry about that, and to concatenate the parameters in the way that was most useful for reuse / documenting (as you have mentioned) ... so I did ... and he was right, its made no discernible difference to performance (and unlike your casual use we log millions and millions of rows a day). So I changed it so that each Sproc (that calls the Logger) concatenates all parameters together with @ParamName labels suitable for reuse. The only thing I don't bother to do, because I thought it was a) excessive and b) rarely needed, is that I don't use REPLACE to double-up single quotes - so any '
in the data will break any attempt to use the log-values as-is.
All our Sprocs have param-1 as the Session ID. We are all web-based here, so SessionID this is a pretty universal value for us. What it does mean is that I can see every Sproc used (including when Proc-A calls ChildProc-B) for a given session. We also log all the pages that were rendered and also the "phone-home" from within the page once the Client has rendered it, which tells us the point-in-time at which it was available to the client.
So when viewing the log for a specific session I can see the time of the request for the page, the elapsed time of each, and all, the Sprocs that were executed, and then the phone-home time of the fully rendered page on the client (or if there is an error the log shows that the process only completed part way through). We use this info for performance checking (both to alert if anything starts running slower than normal, and also to figure out what needs optimising - without having to run Profiler all day long ...)
ALTER PROCEDURE dbo.CreateLogRecord
@SessionID int -- Session ID (in SessionLog table)
, @SprocName varchar(255) -- SP Name (or Job Name etc.)
, @Params varchar(8000) -- Concatenated string of parameter values
, @Special1 varchar(8000) = NULL -- Other data
, @Special2 varchar(8000) = NULL
, @Special3 varchar(8000) = NULL
, @Special4 varchar(8000) = NULL
, @Special5 varchar(MAX) = NULL
, @blnTransNoLog bit = 1 -- 0=Always log, 1=Only log if NOT in a transaction
, @blnBusyNoLog bit = 1 -- 0=Always log, 1=Only log if NOT Busy
-- Set to 1 during Busy Periods
, @blnIsBusy bit = 0 -- Do NOT pass this parameter, it is for DBA Control only.
, @LogRecordID int = NULL OUTPUT -- ID in PageProcedureLog table. Pass to UpdateLogRecord
@Params is truncated at 8000 CHAR just to be reasonably economical, if I really need to log a varchar(MAX) I use @Special5
There is some stuff here in case the logging got too busy - our extremely frequently called Sprocs set @blnBusyNoLog=1, and they optionally are not logged during busy periods. We change the default @blnIsBusy=1 and RECreate this Sproc (rather than reading a config value from a table, which we thought would be too slow) if we have an emergency need not to log the very frequently called SProcs, but we've only ever needed this on a handful of occasions and that was because of some other slow-code, nothing to do with the actually logging itself - as it turned out, but I didn't known that originally. So: I really don't think that's necessary (and not relevant in your use-case anyway, so just mentioning it for completeness and anyone else reading this in the future)
ALTER PROCEDURE dbo.UpdateLogRecord
@LogRecordID int -- ID in PageProcedureLog table. NULL will create a row
, @ReturnVal int
, @ErrMsg varchar(8000)
, @RowCount int = NULL
, @ErrNo int = NULL
, @Special1 varchar(8000) = NULL -- Other data - only stored if NOT NULL
, @Special2 varchar(8000) = NULL
, @Special3 varchar(8000) = NULL
, @Special4 varchar(8000) = NULL
, @Special5 varchar(MAX) = NULL
, @Params varchar(8000) = NULL -- Only used if CreateLogRecord was not used
Updating the log row basically just stores the ReturnValue and any useful values in the "Special" columns. I thought that calling a "CreateLogRecord" and then an "UpdateLogRecord" SProc pair might be extravagant, so the UpdateLogRecord has the ability to create a record (@LogRecordID=NULL and provide a value for @Params) but I was reluctant to use it like that because I lose the ability to detect Sprocs that fail to complete (e.g. DEADLOCK) and, in practice, I've never had a performance problem [With Create + Update] so I've never actually made use of it. But I CAN use UpdateLogRecord to just "log some interesting value" (can't do that with CreateLogRecord because it sets the ErrNo column to the "Assumed failed" value unless there is also an UpdateLogRecord).
We sometimes log "interesting values" within Sprocs, particularly "This should never happen" or "You might like to know this is STILL happening"! I can store a pseudo-error-no so that it shows up in Error Reports (i.e. a non-zero outcome value)
Within the body of all Sprocs the LogCreate and LogUpdate looks like this:
SELECT @strParameters = LEFT(
'@SomeID=' + COALESCE(CONVERT(varchar(20), @SomeID), 'NULL')
+ ',@MyString=' + COALESCE(''''+@MyString+'''', 'NULL')
+ ',@MyInt=' + COALESCE(CONVERT(varchar(20), @MyInt), 'NULL')
+ ',@MyDateTime=' + COALESCE(''''+CONVERT(varchar(24), @MyDateTime, 113)+'''', 'NULL')
+ ',@MyDate=' + COALESCE(''''+CONVERT(varchar(24), @MyDate, 113)+'''', 'NULL')
+ ',@MyGUID=' + COALESCE(''''+CONVERT(varchar(40), @MyGUID)+'''', 'NULL')
, 8000)
So : concatenate all parameters, then create the log record:
EXEC @LogRecordID = dbo.CreateLogRecord @SessionID, 'MySprocName', @strParameters
, @Special1='Some Special Value' -- optional, almost never used at CREATE
then just before the RETURN update the log record with the outcome
EXEC dbo.UpdateLogRecord @LogRecordID, @intReturnVal, @strErrMsg, @intRowCount, @intErrNo
-- , @Special1=@strSpecial1, @Special2=@strSpecial2, ...
IF @intDebug >= 1 SELECT [MySprocName]='RETURN', [@intRetVal]=@intRetVal, [Elapsed (sec)] = DATEDIFF(Second, @StartTime, GetDate()), [@intErrNo]=@intErrNo, [@@TRANCOUNT]=@@TRANCOUNT, [@strErrMsg]=@strErrMsg, *
FROM dbo.PageProcedureLog_Short_View WHERE LogRecordID = @LogRecordID
The @intDebug is a parameter to all Sprocs which allows easy diagnostics of an Sproc. The [PageProcedureLog_Short_View] is basically all columns in the PageProcedureLog table but all truncated so that, collectively, they don't cause any overflow of output for whatever SQL tool is being used (not sure if this would be a problem in SSMS, its not a problem in Grid Mode, but I use Text Mode and Query Analyser [still! ... sorry about that!] and that has limitations.
So in my log record I wind up with:
SprocName = 'MySproc'
SessionID = 1234
Params = @Param1=1234,@Param2='ABCD', ...
and I can easily use those to re-run the Sproc for testing:
BEGIN TRANSACTION
EXEC MySproc 1234, @Param1=1234,@Param2='ABCD', ...
, @intDebug=01
ROLLBACK
I know nothing about F#, am not sure why you need dynamic code??, and quite possibility all this wibble is totally irrelevant - if so: sorry about that 