SQL Database Connector pagination solution (MS SQL)

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

5 Likes

Thank you!

There is also this solution (which is similar to yours): [Solved] SQL Database Request with FETCH - #2 by ben13

And if you really don’t want to deal with the limit you can you can try our paid plugin: SQL Database Connector Pro Plugin | Bubble

1 Like