Compatibility of STRING_SPLIT function inside store procedure for Azure synapse serverless sql pool

Question

I am trying to create a stored procedure in Azure synapse serverless sql pool. The STRING_SPLIT function works fine when used in a select statement but not inside store procedure. I receive an error that it is not recognized. The database compatibility level as 150 which should allow for the function.

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

error: 'STRING_SPLIT' is not a recognized built-in function name.

1. Is it possible to use STRING_SPLIT function inside procedure?
2. Is there any documentation that mentions compatibility of STRING_SPLIT?
3. If it is not compatible then are there any alternatives?

CREATE PROCEDURE [dbo].[sp_generate_combinations]
    @listOfDimensions NVARCHAR(MAX) = 'activity_date,activity_country,lang,region,age_range',
    @crunchLevels NVARCHAR(MAX) = '1,2,3',
    @filters NVARCHAR(max) = ' WHERE groupBy LIKE ''%activity_date%'' ' -- filter for only combinations having activity date
AS
BEGIN
    -- Create temporary tables to store split strings
    CREATE TABLE #Dimensions (Value NVARCHAR(MAX));
    CREATE TABLE #CrunchLevels (Value NVARCHAR(MAX));


    -- Split strings and insert into temp tables
    INSERT INTO #Dimensions
    SELECT TRIM(value) FROM STRING_SPLIT(@listOfDimensions, ',');

    INSERT INTO #CrunchLevels
    SELECT TRIM(value) FROM STRING_SPLIT(@crunchLevels, ',');


    -- Print the split results
    PRINT 'Dimensions:';
    SELECT Value FROM #Dimensions;

    PRINT 'Crunch Levels:';
    SELECT Value FROM #CrunchLevels;


    -- Clean up temporary tables
    DROP TABLE #Dimensions;
    DROP TABLE #CrunchLevels;

    PRINT 'filters: ' + STRING_SPLIT(@filters)

END;

Answer 1

Hi @Suraj Shashidhar
I see that you are trying to use the STRING_SPLIT function inside a stored procedure in Azure Synapse Serverless SQL Pool. Based on the provided SQL code snippet, it seems that you are encountering an error stating that 'STRING_SPLIT' is not a recognized built-in function name when using it inside the stored procedure.

Is it possible to use STRING_SPLIT function inside procedure?

STRING_SPLIT isn't supported in Azure Synapse Serverless SQL Pool, which is why you're seeing the error inside the stored procedure. Even though the database compatibility level is 150, some SQL Server functions, including STRING_SPLIT, are not available in the serverless environment.

Is there any documentation that mentions compatibility of STRING_SPLIT?

Microsoft doesn’t explicitly list STRING_SPLIT as unsupported, but you can check the Azure Synapse T-SQL feature support for function availability.

If it is not compatible then are there any alternatives?

Since STRING_SPLIT isn't supported, you can try using XML parsing or JSON functions instead. Here's a simple workaround using OPENJSON:

DECLARE @listOfDimensions NVARCHAR(MAX) = 'activity_date,activity_country,lang,region,age_range';
SELECT value 
FROM OPENJSON('["' + REPLACE(@listOfDimensions, ',', '","') + '"]') 
WITH (value NVARCHAR(MAX) '$');

This approach converts your comma-separated string into a JSON array and splits it using OPENJSON, which works in Synapse Serverless.

I hope this information helps. If you have any more questions let me know.