For 1/0
specifically, you can prevent the error by running the following SET
statements (note that I don't recommend this as a "solution" to your problem, as turning those settings off can cause a lot of confusion and hide important errors):
SET ANSI_WARNINGS, ARITHABORT OFF;
The ORDER BY
clause supports specifying a column in the select list by it's ordinal position to sort by. In other words, "ORDER BY 1
" means to order by the first item in the select list.
This example uses the "AdventureWorks" sample database from Microsoft:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2;

SQL Server does not support constant expressions though:
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 2-1;
Msg 408, Level 16, State 1, Line 18
A constant expression was encountered in the ORDER BY list, position 1.
In your case, 1/0
is a constant expression. However, since calculating that would result in an error, SQL Server treats it differently. You can read about that in Itzik Ben Gan's article Row numbers with nondeterministic order:
What happens is that on one hand, SQL Server fails to apply constant folding, and therefore the ordering is based on an expression that is not a single constant. On the other hand, the optimizer figures that the ordering value is the same for all rows, so it ignores the ordering expression altogether.
You can see that in the execution plan if we run the 1/0
version of the query with those two settings off:
SET ANSI_WARNINGS, ARITHABORT OFF;
GO
SET STATISTICS XML ON;
GO
SELECT p.BusinessEntityID, p.FirstName
FROM Person.Person p
ORDER BY 1/0;

In this case, you can see there is not sort operation. The Compute Scalar attempts to calculate 1/0
, but fails. Since those two settings are off, the "divide by zero" error is suppressed so the query completes (with a nondeterministic sort order).
A better solution for dynamic sorting is discussed in Erland Sommarskog's article Dynamic Search Conditions in T‑SQL. The gist of that solution is to use a CASE
statement to transform the user input sort column into a known column value:
SELECT @sql += ' ORDER BY ' +
CASE @sortcol WHEN 'OrderID' THEN 'o.OrderID'
WHEN 'EmplyoeeID' THEN 'o.EmployeeID'
WHEN 'ProductID' THEN 'od.ProductID'
WHEN 'CustomerName' THEN 'c.CompanyName'
WHEN 'ProductName' THEN 'p.ProductName'
ELSE 'o.OrderID'
END + CASE @isdesc WHEN 0 THEN ' ASC' ELSE ' DESC' END
This prevents unexpected values from affecting query execution, and helps protect against SQL injection.
I don't know how viable this approach is for you, since you appear to be trying to maintain compatibility between multiple database platforms.