In SQL 2016 I had some functions that contained selects with order by [position] instead of [column name]. They stopped working in 2022. Now they return the error The ORDER BY position number 2 is out of range of the number of items in the select list. The interesting thing is that the Order by [position] is still supported in 2022, and that if I take the content of the function and run it as a script it works fine.
See sample below. Please, don't try to make sense of the content of the query. It is just a test and I used msdb tables that everybody has so you can try it yourself.
Create the following function
create FUNCTION [dbo].[TestingOrderBy]() returns INT
as
begin
declare @VR int, @Order int
select top 1
@VR = [version_revision],
@Order = case when L.[id] > 1000 then 1 else 99 end
from msdb.[dbo].[msdb_version] L
order by 2 asc, L.[version_major] desc
return @VR
end
When you execute it in SQL 2022 as shown below, you will receive an error. You will not receive an error if you execute it in SQL 2016. The error is The ORDER BY position number 2 is out of range of the number of items in the select list
select dbo.TestingOrderBy()
Now, if you simply execute the content of the function, as the following code does, it runs fine.
declare @VR int, @Order int
select top 1
@VR = [version_revision],
@Order = case when L.[id] > 1000 then 1 else 99 end
from msdb.[dbo].[msdb_version] L
order by 2 asc, L.[version_major] desc
select @VR
Whyyyy?????
