{"id":3944,"date":"2024-07-16T08:12:55","date_gmt":"2024-07-16T01:12:55","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3944"},"modified":"2024-07-22T07:52:12","modified_gmt":"2024-07-22T00:52:12","slug":"nodejs-sql-server-offset-fetch","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-offset-fetch\/","title":{"rendered":"Node.js SQL Server: Pagination"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to perform pagination using the OFFSET<\/code> and FETCH<\/code> clauses in Node.js.<\/p>\n\n\n\n

This tutorial begins where\u00a0the Selecting data from a table in Node.js tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Pagination is a technique for breaking up a large dataset into smaller, more manageable chunks (pages) for display purposes.<\/p>\n\n\n\n

In SQL Server, you can use the OFFSET<\/code> and FETCH<\/code> clauses<\/a> together to implement pagination.<\/p>\n\n\n\n

Pagination using OFFSET and FETCH clauses #<\/a><\/h2>\n\n\n\n

We’ll retrieve data from the Authors<\/code> table using the OFFSET<\/code> and FETCH<\/code> clauses.<\/p>\n\n\n\n

Step 1. Modify the select.js<\/code> file and define a new function findAuthors<\/code> that retrieves the authors using the OFFSET<\/code> and FETCH<\/code> clauses:<\/p>\n\n\n

async<\/span> function<\/span> findAuthors<\/span>(limit = 5<\/span>, offset = 0<\/span><\/span>) <\/span>{\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    const<\/span> pool = await<\/span> sql.connect(config);\n\n    \/\/ Execute a query<\/span>\n    const<\/span> result = await<\/span> pool\n      .request()\n      .input('offset'<\/span>, sql.Int, offset)\n      .input('limit'<\/span>, sql.Int, limit)\n      .query(\n        `SELECT * FROM Authors \n         ORDER BY FirstName\n         OFFSET @offset ROWS \n         FETCH FIRST @limit ROWS ONLY`<\/span>\n      );\n\n    return<\/span> result.recordset;\n  } catch<\/span> (err) {\n    console<\/span>.error('Error finding authors:'<\/span>, err);\n  }\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, connect to the SQL Server:<\/p>\n\n\n
const<\/span> pool = await<\/span> sql.connect(config);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, retrieve the authors using the OFFSET<\/code> and FETCH<\/code> clauses sorted by the FirstName<\/code> in ascending order:<\/p>\n\n\n
const<\/span> result = await<\/span> pool\n  .request()\n  .input('offset'<\/span>, sql.Int, offset)\n  .input('limit'<\/span>, sql.Int, limit)\n  .query(\n    `SELECT * FROM Authors \n         ORDER BY FirstName\n         OFFSET @offset ROWS \n         FETCH FIRST @limit ROWS ONLY`<\/span>\n  );<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement:<\/p>\n\n\n\n