Why does stored procedure invalidate SQL Cache Dependency?

Question

After many hours, I finally realize that I am working correctly with the Cache object in my ASP.NET application but my stored procedures stops it from working correctly.

This stored procedure works correctly:

CREATE PROCEDURE [dbo].[ListLanguages]
@Page INT = 1,
@ItemsPerPage INT = 10,
@OrderBy NVARCHAR (100) = 'ID',
@OrderDirection NVARCHAR(4) = 'DESC'
AS
BEGIN
    SELECT ID, [Name], Flag, IsDefault FROM dbo.Languages
END

But this (the one I wanted) doesn't:

CREATE PROCEDURE [dbo].[ListLanguages]
@Page INT = 1,
@ItemsPerPage INT = 10,
@OrderBy NVARCHAR (100) = 'ID',
@OrderDirection NVARCHAR(4) = 'DESC',
@TotalRecords INT OUTPUT
AS
BEGIN
    SET @TotalRecords = 10

EXEC('SELECT ID, Name, Flag, IsDefault FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderDirection + ') as Row, ID, Name, Flag, IsDefault
    FROM dbo.Languages) results
    WHERE Row BETWEEN ((' + @Page + '-1)*' + @ItemsPerPage + '+1) AND (' + @Page + '*' + @ItemsPerPage + ')')
END

I gave the @TotalRecords parameter the value 10 so you can be sure that the problem is not from the COUNT(*) function which I know is not supported well.

Also, when I run it from SQL Server Management Studio, it does exactly what it should do. In the ASP.NET application the results are retrieved correctly, only the cache is somehow unable to work!

Can you please help?

Maybe a hint

I believe that the reason why the dependency HasChanged property is related to the fact that the column Row generated from the ROW_NUMBER is only temporary and, therefore, the SQL SERVER is not able to to say whether the results are changed or not. That's why HasChanged is always set to true.

Does anyone know how to paginate results from SQL SERVER without using COUNT or ROW_NUMBER functions?

Answer 1

Sql cache dependency for .NET 3.5 only works for simple queries. Maybe .NET 4 will surprise me.

Answer 2

not enough cache size.

Answer 3

1 - Can you copy & paste the code you actually use to cache the results of that sproc ? 2 - Have you tried a sproc where you use straight query instead of EXEC-ing a string ?

Yes #2 means that you can't change the structure of the query on the fly :-) but unless you are calculating your own caching criteria in #1 that's the rule of caching you have to abide by in general. No caching mechanism is ever going to parse a string from EXEC from you.

EXEC-ing a string in a sproc makes that sproc a total toss of a coin on each and every run even for SQL Server itself. It also leaves you open to script injection attacks since your query is still being composed by strings at run time - it's not any different from composing the whole string in C# and passing it to sproc to "just EXEC whatever is inside"