Synapse SQL Serverless: How can I create a function that returns a single text value

Question

Hi, I am using Synapse SQL Serverless and all I want to do is create a function that splits a string into rows using the STRING_SPLIT function and substitute those values into another string that has placeholders for those values. However, functions do not allow me to use cursors, temp tables or types. So I created a procedure that puts in the values in a temp table then I loop round the values in the temp table and do my required processing and output a string at the end. However, I want to use the value returned in the procedure in a SQL query. However, when I create a function that calls this procedure I get "RETURNS <return_data_type> is not supported for CREATE/ALTER FUNCTION." What can I do to resolve this?

Thanks for any help provided

Example data for Proc which comes from a select query:
text: 'I have %1s apples, %2s oranges, %3s pears, %4s bananas and %5s lemons'
notes: '2:4:3:2:6'

Required output: 'I have 2 apples, 4 oranges, 3 pears, 2 bananas and 6 lemons'

Code:

CREATE OR ALTER PROCEDURE procValidationDetails (
    @notes NVARCHAR(50), 
    @text NVARCHAR(300),
    @full_description NVARCHAR(300) OUTPUT
) 
AS 
BEGIN
    DECLARE @description VARCHAR(300) = @text
    DECLARE @cnt SMALLINT
    DECLARE @note VARCHAR(30)

    IF OBJECT_ID(N'tempdb..#notes_table') IS NOT NULL
    BEGIN
       DROP TABLE #notes_table
    END

    CREATE TABLE #notes_table (VALUE NVARCHAR(30));

    INSERT INTO #notes_table SELECT VALUE FROM STRING_SPLIT(@notes,':')  

    SET @cnt = 1

    DECLARE @recCount INT = (SELECT COUNT(*) FROM #notes_table)

    WHILE @cnt <= @recCount
    BEGIN

        SET @note = (                        
            SELECT VALUE 
            FROM (
                SELECT  VALUE, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN 
                FROM    #notes_table
            ) AS T
            WHERE RN = @cnt
        )

        SET @description = REPLACE(@description, '%' + CAST(@cnt AS VARCHAR) + 's', @note)

        SET @cnt = @cnt + 1

    END

    SET @full_description = @description

    RETURN

END

CREATE FUNCTION getVAlidationDetails (
     @notes NVARCHAR(30),
     @description NVARCHAR(300) 
) RETURNS VARCHAR
AS
BEGIN
    DECLARE @fullDescription NVARCHAR(300)
    EXECUTE procValidationDetails @notes, @description, @full_description = @fullDescription OUTPUT
    RETURN @fullDescription
END

Answer 1

the error "RETURNS <return_data_type> is not supported for CREATE/ALTER FUNCTION." is thrown because scalar functions are not supported in synapse serverless SQL
see: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features
"only inline table-valued functions are supported. Scalar user-defined functions are not supported"
and of course that you cannot execute a stored procedure in a function

Answer 2

What can I do to resolve this?

Functions are supported by Synapse, see CREATE FUNCTION (Azure Synapse Analytics)
But we don't know a single line of your code, so no one can say, what causes the error.

Answer 3

There is a note in the topic that Olaf referred to which says: This syntax is not supported by serverless SQL pool in Azure Synapse Analytics. As it is appears between two syntax graphs, it is not clear which syntax it refers to, but judging from the error message you get, it seems to be the syntax above the note, that is the one you attempted.

Apart from that, you are trying to call a stored procedure inside the function. I don't use Synapse myself, so I can't speak with authority. But in plain SQL Server at least, you cannot call stored procedures in functions.

Answer 4

Since I don't work on Synapse and serverless pools, I can't test what works and what does not. However, I have an article with plenty of methods to split a separated list into table format. Some of these methods may work on Synapse serverless. See https://www.sommarskog.se/arrays-in-sql-2005.html.