MartJ (12/19/2012)

---

Hi all

I have a similar issue so I though a more in depth description may help vineetbhargav too.

I have a proc that is used in a report. The report has shared datasets that work out all related dates that are used in the reports eg. start of year, start of month etc The report pass a parameter @YStartDate, it's a date, to the stored procedure.

The WHERE clause of my query within the proc is WHERE deal_date > @YStartDate

The Filter on my index is (deal_date > '20120401') - I intended to run a job to update the value when necessary ... if I can get it working.

The value passed is from a shared dataset and is currently '20120401' but will obviously change when we move to the next financial year.

What happens:

When the procedure is executed the index is not used. If I hard code the value in the WHERE clause it is. I've tried WITH RECOMPILE and OPTION (RECOMPILE) to try and get a new plan when the parameter is passed but no joy.

The optimizer is clearly unaware that the index can be used and a hint to use it causes an error so I've resorted to using (deal_date is not null) instead.

Any help on ways around this, other than dynamic sql, would be very much appreciated.

Many thanks!