Question:

Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains?

Possible Solution:

I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user would still get the latest data. Please speak to this as well.

Given Example:

Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc.

Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees 5/1/2010 to 5/4/2010. But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range.

Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question.

Example proc and execution (if that helps to understand):

CREATE PROCEDURE GetFooData
    @StartDate datetime
    @EndDate datetime
AS

    SELECT *
    FROM Foo
    WHERE LogDate >= @StartDate
    AND LogDate < @EndDate

Here's a sample execution using DateTime.Now:

EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now

Here's a sample execution using DateTime.Today.AddDays(1)

EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1)

The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.