Parameter Optimization

Hi guys,
I have a rather complex query which accepts 2 date parameters. When the
dates are hard-coded the query runs in 2 seconds; however when a parameter
is passed with the same values the query runs in excess of 2 minutes.
I got around this by building the entire query into a varchar variable, and
then using the exec(strexpression).
Is there a problem in the way the SQL server optimizer handles parameterized
queries? And what are the alternate ways to get around this?
Thanks,
JustinMaybe post some ddl on this specifying what the datatypes are.
"Justin" wrote:

> Hi guys,
>
> I have a rather complex query which accepts 2 date parameters. When the
> dates are hard-coded the query runs in 2 seconds; however when a parameter
> is passed with the same values the query runs in excess of 2 minutes.
>
> I got around this by building the entire query into a varchar variable, an
d
> then using the exec(strexpression).
>
> Is there a problem in the way the SQL server optimizer handles parameteriz
ed
> queries? And what are the alternate ways to get around this?
>
> Thanks,
> Justin
>
>|||I noticed that if you have many records in tables, conversion from varchar
to nvarchar or back can cause behavior you talk about. maybe when you
hardcode dates u use different type than when you pass them as params?
peter