question

Apeman avatar image
Apeman 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?
t-sqlstored-proceduresparameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Within the proc. I am currently logging procname, starttime and endtime, but the runtime depends on the value of the parameters. That's why I want the values.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dillinzser avatar image
dillinzser answered
Why don't you create a logger procedure that you can call from inside the SPs that you want to log.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It doesn't matter if logging is done by an SP; I just want the value of parameters (which seems to be impossible)
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.