Get exec string inside stored procedure

Question

Inside a stored procedure, how do I get the calling command line? So if my stored procedure was called like:

exec UPD_ProjectChecklistField @ProjectRecID = N'19', 
                               @FieldName = N'chkProjectTags',
                               @Value = 1,
                               @UserID = N'jnelson'

Inside the proc I want to get that string so I can add it to some auditing. I know I could assemble it by hand but that would need to be customized in every single proc. Is there anything I can call (view, etc.) that would give me that? Kind of like an arguments list in most programming language functions like @args. I can get the proc name using OBJECT_NAME(@@PROCID) but need the whole call with parameters and values.

Any insight?

Tab Alleman · Accepted Answer · 2016-04-08 17:39:44Z

1

Inside the proc, no.

But you can capture stored procedure calls with Profiler.

Or, if you're only interested in calls that come from a specific application, the DAL would be a good place to handle this.

answered Apr 8, 2016 at 17:39

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JoelCool Over a year ago

I'm hoping that's not the only answer Tab. I do use the profiler when needed of course. Seems like if the profiler can get it I should be able to get it while in the proc itself. And it seems like such a trivial thing. By the way, what is DAL?

Tab Alleman Over a year ago

DAL = Data Access Layer. The section of your application code where you put together the SQL Command and send it to the database. It's an ideal place to also log the command you just sent.

JoelCool Over a year ago

Right, thought you were referring to a DAL in Sql Server. So yes, I can do that there or in the proc itself. Either way every call is manually customized or I can create a function or proc that I could pass the parameters to which would return a string representation as needed. Might have to do it that way. Sigh!

Tab Alleman Over a year ago

Well that depends on your DAL. In most shops I've worked, we've created a DAL function that generically handles putting the Command together. So everywhere in the application, if you want to call a proc, you call this one function. You pass it the proc name and an array of parameters, and the function puts the SQL Command together and executes it. So you could put logging code in that one function, and it would handle logging all of your proc calls application-wide without having to change the code anywhere else in the app.

JoelCool Over a year ago

I like that approach and may do just that. I'm going to do some research first on how to find the same information the profiler gets to see if that's possible. Of course that means hitting the database even more. Thanks for your suggestions!

|

Collectives™ on Stack Overflow

Get exec string inside stored procedure

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related