1

Since you can't get the values of parameters of running queries in SQL Server I was thinking of appending the parameter values to the query in the form of comments that would then be visible via sp_who2. For example If I have this query:

SELECT FirstName, LastName
FROM MyTable
WHERE FirstName = @P1
AND LastName = @P2

I would append the parameters to the query before submitting it so that it would be passed to the database looking like this:

SELECT FirstName, LastName
FROM MyTable
WHERE FirstName = @P1
AND LastName = @P2
/*
@P1 = John
@P2 = Doe
*/

I know that CFParam returns an array of the parameters passed in but that's only after the query result is returned. How can I go about doing this before the query is passed to the database?

Thanks!

Leigh
  • 28,765
  • 10
  • 55
  • 103
user3570706
  • 225
  • 1
  • 2
  • 7
  • If you want the values of your params before you pass it to the query simply select from params before you pass them to your query, Isnt it this simple? – M.Ali Apr 24 '14 at 21:32
  • 4
    Can you elaborate on why you need to do this ie What is the ultimate goal here? Also, what happened when you tried it? – Leigh Apr 24 '14 at 22:35
  • I probably should have mentioned up front that I'm a DBA not a CF programmer, so I don't know much about CF. Thus I haven't actually tried anything. I'm just researching options that I can go to our developers with. The goal is to easily be able to see the parameters that are being passed to queries. The only way to capture those parameters in SQL Server is via a profiler trace but I don't want to keep one running all the time. If the parameters were included in a comment as outlined above then I could see the parameters with a sp_who2. – user3570706 Apr 25 '14 at 13:29
  • 1
    _"I'm just researching options"_ - That's not what [Stack Overflow is for](http://stackoverflow.com/tour). (Maybe you want a discussion forum, like [cf-talk](http://houseoffusion.com/groups/cf-talk/).) However it also sounds like you're inventing a problem that doesn't really exist - when debugging needs to be done, turn the profiler on, otherwise leave it off - you haven't presented a reason for needing to know params before/without a query being executed. – Peter Boughton Apr 25 '14 at 15:42
  • 3
    I'm not discussing, I'm _trying_ to point you in the right direction (whilst clarifying that SO isn't just a generic help forum). The solution for what you've asked has already been given (and could have been received sooner just by talking to the your developers), but - in the absence of a reason for it - what you've asked [sounds like the wrong thing to be doing](http://mywiki.wooledge.org/XyProblem). – Peter Boughton Apr 25 '14 at 17:28

0 Answers0