DavidM

Most SQL Server based apps use stored procedures exclusively, so the idea of forcing SQL Server to ignore plan caching is nasty.

According to the BOL, it is "unusual" to use this option.

But I think I have one...

My application has an end user designable reporting component. Put simply, the end user is allowed to create and design reports at runtime.  Users are allowed to either construct queries or browse from a predefined list.  Using the resultant dataset, they can then design a report.

I know some of you would be horrified by the fact that users are allowed to write their own queries.  Security, performance blah, blah the list goes on...  Basically when a user is creating  and testing these queries, the application does 2 things to protect the database.

1. The sql connection that the application uses changes to a sql account that only has datareader priviliges.
2. The application first adds SET PARSEONLY ON to the top of the statment. 
3. If the sql is valid, the application adds SET ROWCOUNT 500 to the top of the statement and then runs it.

Anyway, these "queries" are stored in a db table and end up as "predefined" queries.

Execution of a predefined query is done through a storedprocedure.. Below is a very shortened example..

CREATE proc dbo.upQuery_Executer
@StartDate DateTime = NULL,
@EndDate DateTime = NULL,
@QueryName VARCHAR(255)
with recompile
AS
SET NOCOUNT ON
DECLARE @TSQL VARCHAR(8000)

SELECT @TSQL = TSQL
FROM dbo.Query WHERE QueryName = @QueryName

EXEC sp_executesql @TSQL, N'@StartDate datetime, @EndDate datetime', @StartDate = @StartDate, @EndDate = @EndDate
GO

Does adding "with recompile" here makes sense?  I would have thought so.

Or does the context change (sp_executesql) make the option useless?

Thoughts?