MSSQL Variable, Case, Offset, Fetch, Collasesc

MSSQL does not have MYSQL limit and offset, it uses fetch and offset instead.

Note MSSQL fetch and offset are available for MSSQL 2012 +, and must be used after order by.
If you do not want order by use (SELECT NULL)

SELECT * 
FROM AM_API
ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

https://dba.stackexchange.com/questions/167562/how-to-solve-invalid-usage-of-the-option-next-in-the-fetch-statement

https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/

MSSQL Case are like if statement :

offset case when @PageNumber > 0 and @PageSize > 0 then @PageSize * (@PageNumber - 1) else 0 end rows
fetch next case when @PageSize > 0 then @PageSize else 100 end rows only

https://arstechnica.com/civis/viewtopic.php?f=20&t=1454557
https://www.w3schools.com/sql/sql_case.asp

To do the comparison in CASES, need to declare a variable on top of select statement :

DECLARE

@PageNum  INT,
@PageSize INT

...

SELECT @PageSize = COALESCE(@PageSize, 2000000000);   
-- 2 billion should be enough?

... OFFSET (COALESCE(@PageNum, 1)-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

OR 

DECLARE 
    @test   int,
    @result char(10)

SET @test = 10

SET @result = CASE @test
    WHEN 10 THEN 
        'OK test'
    ELSE
        'Test is not OK'
END

PRINT @result;

SET @result = CASE 
    WHEN @test = 10 THEN 
        'OK test'
    ELSE
        'Test is not OK'
END

PRINT @result

To fetch all records use (2 billion records) :

fetch next case when @PageSize > 0 then @PageSize else 2000000 end rows only

https://stackoverflow.com/questions/12375837/offset-fetch-next-to-get-all-rows

https://stackoverflow.com/questions/3403390/sql-case-and-local-variables/3403424

OR you can assign to a PHP variable 

        DECLARE 
        @item   int
        SET @item = $item_per_page
        SELECT

Declaration of variable must be done before SELECT

Coalaescs():

Return the first non-null value in a list:

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

https://www.w3schools.com/sql/func_sqlserver_coalesce.asp