SQL : Paging in Stored Procedure

Create simple Student table using following script

CREATE TABLE [dbo].[Student]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]GO

The OFFSET-FETCH clause is used to fetch specif records. OFFSET-FETCH can be used only with the ORDER BY clause.

Stored Procedure :


Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s