Why does stored procedure invalidate SQL Cache Dependency?
- by Fabio Milheiro
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?
