We cannot know for sure  without both execution plans. Below are three common reasons.


**Filtered indexes**

It could be the use of a filtered index such as:


			CREATE INDEX IX_Order_ProductCode_CustomerId_Filtered
			ON [dbo].[Order](ProductCode,CustomerId)
			INCLUDE(date_inserted)
			WHERE date_inserted >= '2019-06-01';

This should pop up as an unmatched indexes warning in the execution plan. More on these [here][1]


**Parameter sniffing**


Another common reason would be parameter sniffing, where the value that runs with the stored procedure first / on plan creation is cached for following executions, until the plan is recompiled. Recompilation can for example happen due to statistics of one of the referenced tables being updated.

More on parameter sniffing [here][2]

If you want sql server to see the parameter as a hardcoded value then you would have to add `OPTION(RECOMPILE)` to the query.


**Wrong datatypes**

if the `date_inserted` column is a string datatype such as `varchar()` then your query would not be able to seek on the parameter. It would do an implicit conversion of the `varchar()` field to a date field on a residual predicate instead of a seek predicate.

The difference between below two queries could then  be immense


    SELECT * FROM [Order] o 
    WHERE o.date_inserted < '2019-06-01' 
    
    DECLARE @date date = '2019-06-01' 
    
    SELECT * FROM [Order] o 
    WHERE o.date_inserted < @date

[![enter image description here][3]][3]
With more than 2 seconds execution time on the second query

Due to the residual predicate:

[![enter image description here][4]][4]

where the entire `date_inserted` column is converted to a `date` datatype before filtering.

  [1]: https://www.brentozar.com/blitzcache/unmatched-indexes/
  [2]: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
  [3]: https://i.sstatic.net/plG8y.png
  [4]: https://i.sstatic.net/i2CoL.png