Splitter Function in Azure SQL Data Warehouse

Question

I am trying to build a Function that accepts multi-value parameters by referencing the code here. However, I am getting an error creating it. What is wrong here?

CREATE FUNCTION [dbo].[func_CommaDelimitedString] (@val nvarchar(100))
RETURNS @clublist TABLE (clubname nvarchar(55))
BEGIN
    DECLARE @x int = 1
    DECLARE @y int = CHARINDEX(',', @val)
                    
    WHILE @x < LEN(@val) + 1
    BEGIN
        IF @y = 0
            BEGIN
                SET @y = LEN(@val) + 1
            END
        INSERT INTO @clublist (clubname) VALUES (SUBSTRING(@val, @x, @y - @x))
        SET @x = @y + 1
        SET @y = CHARINDEX(',', @val, @x)
    END
    RETURN
END

Msg 103010, Level 16, State 1, Line 1 Parse error at line: 13, column: 15: Incorrect syntax near '@clublist'.

My Environment: Microsoft Azure SQL Data Warehouse - 10.0.10887.0 Jan 23 2020 07:36:54 Copyright (c) Microsoft Corporation

I meant I wanted to use this Function in a Stored Procedure I have created. Instead of passing a single value to parameter in SP, I want to pas multi-values. — Julaayi
I am using Azure SQL DW and it is not working as stated above. — Julaayi

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-02-12T22:39:22

Azure Synapse Analytics SQL Pools (aka Azure SQL DW) doesn't support user-defined table-valued functions, only user-defined scalar-valued functions. See CREATE FUNCTION (SQL Data Warehouse) .

But it does support string_split, so you shouldn't need this particular function.

Splitter Function in Azure SQL Data Warehouse

1 Answers