Hello there,
I struggled a bit with SQL Connector pagination, to go around the 200 rows limit bubble imposes and I thought of sharing here, since it might help someone else’s project.
The the idea is to add two parameters in the connector “Page” and “PageSize”. This SQL code then defines the page number you want and the size of this page, limited to 200.
All you need to do is to add your query in the /* YOUR QUERY GOES HERE */ space, add the parameter and it should work fine.
/* lead_table_cnpj_filter*/
DECLARE @PageNum INT, @Size INT
SET @PageNum = @Page
SET @Size = @PageSize
BEGIN
WITH load_table as (
ROW_NUMBER() OVER(ORDER BY l.sectorId ASC) as row_num,
/* YOUR QUERY GOES HERE */
)
SELECT * FROM load_table
WHERE row_num > IIF(@PageNum<=0, 1, (@PageNum-1)*IIF(@Size>200,200,@Size))
AND row_num <= IIF(@PageNum<=0, IIF(@Size>200,200,@Size), @PageNum*IIF(@Size>200,200,@Size))
END