Seeing the params in a SQL statement

[VBAHole22]

Is there a way to write out a sql statement to the debug window at runtime and have it with the values for params in it instead of just the names of the params?

[Nate Bross]

I don't understand the question, or I am missing something. I don't think there is a way to just write out the values of paramaters in an SQL query. If you are building the query from variables in your program, why wouldn't you just write out those variables after putting them in the SQL.

[VBAHole22]

Yes that is one option.

If I have an insert query with say 5 parameters I can writeLine each of the params after I load them into the parameter.

My question is: Is there a way to WriteLine the SQL itself and see the values in the SQL statement? When you do WriteLine on a parameterized SQL statement you see something like:

 

INSERT into tbl (a,b,c) Values (@a,@b,@c)

 

Where the @ is the parameter. Regardless of what value you set it to you are always going to see the parameter listed as that.

 

But isn't the value of the parameter being swapped into that statement before it gets sent to sql? Why couldn't I see the SQL at that point? Then my 5 lines of WriteLine @a, WriteLine @b .... would be replaced by

 

WriteLine(command.RealCommandText)

[PlausiblyDamp]

Parametrised queries don't work like that, they are not simple text substitutions. The underlying provider supports a mechanism that allows parameters to be passed separately from the actual SQL itself. Edited March 4, 2007 by PlausiblyDamp

[VBAHole22]

Yeah. That's what I was afraid of. But sounds about right. That whole parameterizing thing seems a bit mysterious to me. What is it doing and when?

So basically there is no way for me to know during runtime what values are actually getting to the db. Even if I write out the parameters as I set them, how do I know that those are the values that are reaching the database unless I turn around and query for them? Would the parameterization process ever make changes to my values in order to make them "more acceptable" to the database? I don't think it would but you never know what gets done under the sheets in the name of security.

 

Kinda stinks. I was hoping to avoid having to write the code everytime to see my values before they are executed. Helps a lot during debugging. This also detracts from the approach of using the parameters in the first place. And at a time when everyone, including MS, is pushing to use them to avoid injection attacks.

[PlausiblyDamp]

You could just create a standard routine that expects a command as a parameter and iterate over its parameters collection writing them to the debug window - at least it will only need to be done once then rather than every time you need to do this.

However if your procedure is properly written and tested it should work when you pass the values in anyway ;)

[VBAHole22]

properly written and tested

 

That's where I started with this.

 

Thanks for the help. That routine could be very useful, I had not thought about that. Think I'll write that one.

[Nerseus]

A customer routine is what we use at work - loops through a SqlParam collection and creates a custom set of name/value pairs. We use it for debugging and it works well enough.

 

The SQL Engine DOES eventually get the parameters passed as a human-readable string. If you use SQL Profiler you can see the "exec proc @param='value'" type of call. If you don't like the custom code solution, you may look into intercepting that string and saving it somewhere. If the Profiler can see the string before it's executed, there's a high probability that something exists for you to "hook" into that stream. Good luck!

 

-ner