asked
Determine the values of a parameter
Hi, I know you can query the parameters from a SP with sys.parameters. But are the actual values also stored in a table/DMV? I would like to create a logging mechanism which stores the parameters and their values. For example if I have a SP with parameter @startdate datetime and @journaltype int I would like to have the following code in my SP: 1) create a string which determined the parameters (from sys.parameters) and their value (unknown table). The result will be something like "@startdate='2010-11-11',@journaltype=3" Any ideaes?
The values of the parameters would only be available within the execution scope of the procedure - there is no object (table) that stores these - imagine a procedure running concurrently by 10 users - that would be ten different values. Where do you need to access the values from - within the proc as it is called, or externally?
As per my knowledge its is not possible to do so, because Input Parameters are something thing that user needs to supply, it can be constants, or can be from some tables like values from master tables.
Why don't you create a logger procedure that you can call from inside the SPs that you want to log.
I wanted to point that the parameter values are available _inside_ the SP, so you dont have to look for them anywhere else like ie. in system tables.
