STRING_SPLIT with order not working on SQL Server 2022

Question

I need to use STRING_SPLIT with enabled ordering:

We have upgraded all of our servers to SQL Server 2022 and change the compatibility level to 160, but the following code is not working:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

The error is:

Msg 8748, Level 16, State 1, Line 1 The enable_ordinal argument for STRING_SPLIT only supports constant values (not variables or columns).

The production environment is:

Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) 
    Nov 14 2023 18:33:19 
    Copyright (C) 2022 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

The version of the SSMS is:

We have tested the code on the latest CU:

The only thing I have found is this answer pointing that:

The problem is that SSMS has IntelliSense / tooltips coded without conditional logic based on version, and the code is ahead of the engine. Currently the functionality is only available in Azure SQL Database, Managed Instance, and Synapse.

Still, I am not sure where is the issue - the docs, the engine, the SSMS or something I am not doing right.

Note: the issue is the same on Azure Data Studio.

Answer 1

Your database is using forced parameterization.

SQL Server parameterizes the enable_ordinal option, hence the error:

Msg 8748, Level 16, State 1
The enable_ordinal argument for STRING_SPLIT only supports constant values (not variables or columns).

This parameter should be excluded from forced parameterization. It's a bug.

One possible workaround is to use a constant-foldable expression:

select * from string_split('1,2,3,4,5,6', ',', 1 * 1);

---

If STRING_SPLIT were not available, you'd get a different error:

Msg 8144, Level 16, State 3
Procedure or function string_split has too many arguments specified.

Bug report