I have a stored proc that takes 2 dates as parameters. The dates are used to specify a range of dates for the first in a series of CTEs to limit the scope.
When I run the query in SSMS as a query, it runs in 15 seconds and the query plan shows 81% on a sort.
When I make it a proc with the two date variables as parameters, it runs 19 MINUTES. 46% is on a clustered index scan.
Removing the parameters from the stored proc and adding them as variables inside the proc, it's back to 15 seconds and 81% on a sort.
Can someone explain to me why this would be?