Parameters vs. Dynamic SQL

  • Thread starter Thread starter Raterus
  • Start date Start date
R

Raterus

Ok I know the pro's and con's of this subject
http://www.knowdotnet.com/articles/dynamisql.html

I'm trying to convert my colleagues, but they are stuck on one thing with dynamic sql, when they have a problem it is much easier to get the exact view of the sql statement with the values they've loaded onto it when they debug. So if they see an error they can cut/paste the entire string to query analyzer and investigate.

Is there an easy way to do this when using parameters. Something that would "fill in the blanks" for me, just so I can see what the end result sql would look like?

Thanks,
--Michael
 
You can also look at the values of all the parameters in the command object,
when the error occurred.

You can also probably just write a simple loop, to go through each
parameter, and replace it in the sql string with the value, so you can more
or less look at it.

If you look at SQL profiler, you will see that the parameters do not
actually get replaced in the sql string though, they get sent as variables
that get values assigned.

Ok I know the pro's and con's of this subject
http://www.knowdotnet.com/articles/dynamisql.html

I'm trying to convert my colleagues, but they are stuck on one thing with
dynamic sql, when they have a problem it is much easier to get the exact
view of the sql statement with the values they've loaded onto it when they
debug. So if they see an error they can cut/paste the entire string to
query analyzer and investigate.

Is there an easy way to do this when using parameters. Something that would
"fill in the blanks" for me, just so I can see what the end result sql would
look like?

Thanks,
--Michael
 
Ok I know the pro's and con's of this subject
http://www.knowdotnet.com/articles/dynamisql.html
Click to expand...
I'm trying to convert my colleagues, but they are stuck on one thing with
dynamic sql, when they have a problem it is much easier >to get the exact
view of the sql statement with the values they've loaded onto it when they
debug. So if they see an error they can >cut/paste the entire string to
query analyzer and investigate.
Click to expand...
Is there an easy way to do this when using parameters. Something that
would "fill in the blanks" for me, just so I can see what the >end result
sql would look like?
Click to expand...

You can always paste the commandText into Query Analyzer, and declare the
parameters.

For instance, with
select * from my_table where id = @id
Run this in QA like this:

declare @id int
set @id = 123
select * from my_table where id = @id

This has the added benefit of giving you the same execution plan that your
application gets, where

select * from my_table where id = 132

might get a different plan.

David
 
Back
Top