As you will see from the code below I'm using a load of case statements to provide my users with options to display contracts in different ways.

This one works fine:

PHP Code:
    case "AllContractsTerminated"://complete but not tested with pageing
                
$sql "SELECT COUNT(*) FROM Intranet WHERE (Contract_Status='Terminated')"
        
$query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE (Contract_Status='Terminated'))   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number"
;   
        break; 
But this one below causes an error

PHP Code:
    case "ByCountryA-Z"://complete
                
$sql "SELECT COUNT(*) FROM Intranet";
        
$query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet)   
select * from LIMIT WHERE {limit clause} Order by Country ASC"
;   
        break; 
This is the error:

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 720

Which relates to this line of code:

PHP Code:
while($data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))           

What I have noticed is that if I change (ORDER BY Country ASC) which is text to (ORDER BY ID ASC) which is a number, it works fine then, which is the reason I think why the first case example and the second one doesnt.