T-SQL Skip Take Stored Procedure

T-SQL Skip Take Stored Procedure

For 2005 / 2008 / 2008 R2

;WITH cte AS
(
    SELECT  Journals.JournalId, 
            Journals.Year, 
            Journals.Title, 
            ArticleCategories.ItemText,
            ROW_NUMBER() OVER 
                     (ORDER BY Journals.JournalId,ArticleCategories.ItemText) AS RN
    FROM    Journals LEFT OUTER JOIN
            ArticleCategories 
             ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
)
    SELECT  JournalId, 
            Year, 
            Title, 
            ItemText
FROM cte
WHERE RN BETWEEN 11 AND 20

For 2012 this is simpler

SELECT Journals.JournalId,
       Journals.Year,
       Journals.Title,
       ArticleCategories.ItemText
FROM   Journals
       LEFT OUTER JOIN ArticleCategories
         ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId
ORDER  BY Journals.JournalId,
          ArticleCategories.ItemText 
OFFSET  10 ROWS 
FETCH NEXT 10 ROWS ONLY 

Ignore skip/take in stored procedure

Accept default NULL values and set @Skip and @Take to 0 and very big value when parameters are omitted.

ALTER PROCEDURE YourProcedure
    @Skip INT = NULL,
    @Take INT = NULL
AS
BEGIN

    IF @Skip IS NULL
        SET @Skip = 0

    IF @Take IS NULL
        SET @Take = 999999999

    SELECT
        YourColumn
    FROM
        YourTable
    ORDER BY
        SomeColumn
    OFFSET 
        @Skip ROWS 
    FETCH NEXT 
        @Take ROWS ONLY 

END

Make sure that your max @Take value is always greater than your query result rows.

EF using skip and take on stored procedure

I did encounter such requirements before. Originally, my option was to use stored procedure, but when I realize that data could be thousands, using SP no longer became applicable. Here are the two things I did to do this:

1. I created a view where the result of SP is there. Of course, this contains all the records without filter yet. I used that view to manipulate the records via EF where Take and Skip is alreay applicable.
2. I stick to SP. What I did is to design the SP to allow receiving Take and Skip Count. I created the SP as string the use sp_executesql.

Implement paging (skip / take) functionality with this query

In SQL Server 2012 it is very very easy

SELECT col1, col2, ...
 FROM ...
 WHERE ... 
 ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

If we want to skip ORDER BY we can use

SELECT col1, col2, ...
  ...
 ORDER BY CURRENT_TIMESTAMP
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)

to answer the question:

--SQL SERVER 2012
SELECT PostId FROM 
        ( SELECT PostId, MAX (Datemade) as LastDate
            from dbForumEntry 
            group by PostId 
        ) SubQueryAlias
 order by LastDate desc
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

New key words offset and fetch next (just following SQL standards) were introduced.

But I guess, that you are not using SQL Server 2012, right? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: here

So, this could work in SQL Server 2008:

-- SQL SERVER 2008
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10,@End = 20;

;WITH PostCTE AS 
 ( SELECT PostId, MAX (Datemade) as LastDate
   ,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
   from dbForumEntry 
   group by PostId 
 )
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId

SQL Server 2008 Skip Take with long parameter

Well, if it is necessary at all - expected the case in comments where you have to skip billions to take only 20 - to read as lot as possible data.

• You could use a stored procedure that will get the amount to skip and
  take.
• Another approach would be to execute a plain SQL on your dbContext's database property.

If you want to use LINQ and you have increment IDs you could give following a try - but it is a bit more expensive and it will not cover the long value for data amount to retrieve:

At first execution you have to determine the ID of int.MaxValue-1. After that point you could re-use the logic for rest of paging. (maybe depending on a flag)
Use the ID as where clause (lower border) and add a Take with needed amount. From result's last record you will save the ID for next paging call where you will use it instead of first determined ID.

A possible approach code (not tested) could be (will not cover all your cases without any modifications):

private long _lastId;

public IEnumerable<Students> GetPage(int toTake)
{
    List<Students> result;

    result = isFirstPage
        ? _context.Students
            .Take(toTake)
            .ToList();
        : _context.Students
            .Where(s => s.Id > _lastId)
            .Take(toTake)
            .ToList();

    _lastId = result.LastOrDefault()?.Id ?? 0;

    return result;
}

---

Related Topics