February 25, 2009 at 10:29 pm
Is there a way to dynamically determine stored procedure parameter values within the procedure itself?
For instance
CREATE PROCEDURE s_DoSomething
(
@p1 int,
@p2 varchar(10),
@p3 datetime
)
...
BEGIN
Somewhere within here I'd like to have some boiler plate code that would be able to dynamically determine the names (I know how to get the names so far) of the parameters and the values that were passed in.
END
Is this possible? (and if you say yes, can you point me in the right direction?)
February 26, 2009 at 6:24 am
I'm sorry, I don't understand the question.
When you create a procedure, you define parameters with a certain name and a certain data type, nullability and default values. Within the procedure itself, there's no need to determine the name, it's the same name as you defined initially. As far as determining the values, you just have to reference the parameter. For example:
CREATE PROCEDURE dbo.MyProc
(@Id INT
,@Name VARCHAR(50) = NULL
,@Number TINYINT = 2)
AS
BEGIN
SELECT @Id AS IdValue
,@Name AS NameValue
,@Number AS NumberValue
END
EXEC dbo.MyProc
@Id = 42
,@Name = 'Dude'
/*IdValue NameValue NumberValue
----------- -------------------------------------------------- -----------
42 Dude 2
*/
EXEC dbo.MyProc
@Id = 42
,@Name = 'Dudedette'
,@Number = 5
/*
IdValue NameValue NumberValue
----------- -------------------------------------------------- -----------
42 Dudedette 5
*/
You can use the parameters in IF clauses or whatever.
I'm positive this isn't helping you, so can you clarify what it is you're looking for?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2009 at 7:40 am
The only reason I can think you might be wanting this is to add some kind of standard auditing, so you just drop in a standard block of code and it records which procedure it's in and what the parameters and values are.
I've previously wanted to do a similar thing by calling a separate auditing procedure which would use the 'call stack' (or something) to get the information.
Unfortunately, as far as I am aware, there aren't system functions to do this. But maybe someone else knows better.
The only solution I came up with was to use some incomplete boilerplate code which gets amended as it's edited into the procedure.
Derek
February 26, 2009 at 8:14 am
Oh, you mean something that captures the execution context, that'll show the methods used to call the proc like EXEC blah, blah and include the parameter names & values?
In 2008 Enterprise you can use SQL Audit to do this.
Hmmm... I suspect one of the DMV/DMF's may have that information. Actually, I'd try sys.dm_exec_sql_text. I think that'll do it. You can get there from the session id, @@SPID.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2009 at 3:27 pm
Yes, this is for auditing purposes.
Yes, I know that in each and every procedure I could query the values that the proc was called with, but this is a very messy way to go.
Currently, I use DBCC INPUTBUFFER to get the calling proc & parameters, but this doesn't work if the proc is called from a web application. (bol confirms this). So I'm looking for some way around this. I've looked at the dmv's and I still don't see how I can capture the actual values of the parameters. For example. If I call the proc s_DoSomething 1, 'foo', '1/1/2009' THen somewhere within the code (besides doing this manually) I want to capture the values 1, 'foo', and '1/1/2009' so that I can then audit how this proc was called.
Without this functionality, it appears that it's impossible to capture the actual parameter values that are being executed against the proc without doing a trace or without installing a middle auditing tier before the db.
February 26, 2009 at 8:00 pm
I'd have to do some experimentation with it to be sure, but according to BOL you can get the DMV sys.dm_exec_query_requests has the sql_handle and statement_start_offset combined with statement_end_offset that will allow you to pull the exact statement from sys.dm_exec_query_text. I haven't tried it yet, but it looks right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2009 at 8:12 pm
Hmmm, OK, I'll play with that. It was always returning to me the entire actual code of the procedure. I didn't become intimately familiar with all of the nuances of that and the other dmv's, so I can't say for sure that I wasn't doing it wrong.
February 27, 2009 at 3:00 pm
OK, I've been playing with this and sys.dm_exec_sql_text only returns the code for the procedure. It doesn't return what the values that were passed into the procedure were.
February 27, 2009 at 3:28 pm
I ran into this issue before and found the only way to get this information was to get it from a server side trace. I was not able to find any other way to get the information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2009 at 3:40 pm
I haven't played with SQL 2008 yet. Does anybody know if anything along these lines has been introduced in SQL 2008?
If not, almost makes me want to start sending my parameters in as xml. That way I could have some boilerplate code that could do the auditing.
February 28, 2009 at 5:49 am
David Sumlin (2/27/2009)
I haven't played with SQL 2008 yet. Does anybody know if anything along these lines has been introduced in SQL 2008?If not, almost makes me want to start sending my parameters in as xml. That way I could have some boilerplate code that could do the auditing.
2008 introduces a whole series of calls around a set of functionality called SQL Audit. I know of it, but none of the details.
I tried getting parameters out of the DMV and wasn't able to either. They're just not there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply