The SQL Custom Paging Query for ObjectDataSource, allows each request to only provide data relevant to the particular page, increasing loading speed.
The question is how to make paging optional?
string Query = @"
DECLARE @PagedData int
SET @PagedData = 1
SELECT
CASE
WHEN @PagedData = 1
THEN (SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + _sortColumns + @") AS ResultRank, *
FROM dbTable
WHERE (FeatureString LIKE '%gold%')
AS ProductsWithRowNumber WHERE ResultRank >
" + startIndex + " AND ResultRank <= (" + startIndex + " + " + pageSize + @"))
ELSE (SELECT * FROM dbTable WHERE (FeatureString LIKE '%gold%'))
END";
The goal is for the SQL function to be able to get data only for that particular page, or it can turn off paging get all the relevant data.
Above is my attempt to use a CASE to switch whether the query is paged based on the value of the variable PagedData. However, this method ends in error.
Please let me know if you need me to clarify anything, thank you.
Your SQL does not look exactly right but assuming it is correct, here is how to use an if statement:
string Query = @"
DECLARE @PagedData int
SET @PagedData = 1
IF @PagedData = 1
(
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + _sortColumns + @") AS ResultRank, *
FROM dbTable
WHERE (FeatureString LIKE '%gold%')
AS ProductsWithRowNumber WHERE ResultRank >
" + startIndex + " AND ResultRank <= (" + startIndex + " + " + pageSize + @")
)
ELSE
(
SELECT * FROM dbTable WHERE (FeatureString LIKE '%gold%')
)";