Using OFFSET-FETCH, how to default number of rows to "all rows"?

Question

Envision a stored procedure which takes @skip (offset) and @take (maximum number of rows to return. If @take is null, then I want to return "all rows after applying the offset".

I can accomplish this by counting the number of rows in the table/view if @take is null, but then I have to perform two queries which is, of course, not optimal. I was hoping there was an option similar to FETCH [NEXT] ALL ROWS.

DECLARE @skip BIGINT = 0;
DECLARE @take BIGINT = NULL;

IF (@take IS NULL) SET @take = (SELECT COUNT(*) FROM SomeTable);

SELECT *
FROM SomeTable
ORDER BY SortOrder
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY

Answer 1

You could use COALESCE:

DECLARE @skip BIGINT = 0;
DECLARE @take BIGINT = NULL;

SELECT *
FROM SomeTable
ORDER BY SortOrder
OFFSET COALESCE(@skip,0) ROWS
FETCH NEXT COALESCE(@take,0x7ffffff) ROWS ONLY

LiveDemo

0x7ffffff is the same as 2147483647 max INT value.

When @skip and @take are not provided it will get first 2^31-1 records from table.