I am a bit stuck with the Database Connectors with a Microsoft SQL Database.
I have a request which contains more than 200 entries, I added an button “Show More” to perform a second request on the database.
So I was trying to use the offset and fetch option.
SELECT *
FROM Database
WHERE ((Country LIKE @Country)
ORDER BY Name DESC
OFFSET 200 rows
FETCH 200 rows only
But this command is not allowed by the plugin since it does not contain the command TOP.
You are going to want to do something like the following (I didn’t check this for errors):
SELECT TOP 200 * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Name DESC) AS RowNum FROM dbo.TableName WHERE Country LIKE @Country) AS newTableName WHERE newTableName.RowNum BETWEEN @start AND @end