Create function that returns table doesn't work syntax error? SQL

Question

Hello I need to make a sql request that takes multi parameters in a 'WHERE (abc IN @par1, @par2 ....' To do so, I found some solutions that use a sql function. I tried to use it without any success. I'm working on workbench and this is my function code.

CREATE function MultiStringToTable (InStr VARCHAR(255))
    RETURNS @TempTab TABLE(
   id nvarchar(255) not null
   )
   AS
BEGIN
    -- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',');
    DECLARE @SP INT;
    DECLARE @VALEUR VARCHAR(1000);
    WHILE PATINDEX('%,%', @INSTR ) <> 0 

        SET @SP = PATINDEX('%,%',@INSTR);
        SET @VALEUR = LEFT(@INSTR , @SP - 1);
        SET @INSTR = STUFF(@INSTR, 1, @SP, '');
        INSERT INTO @TempTab(id) VALUES (@VALEUR);
    END;
RETURN;
end;

It causes problem on the '@TempTab'.

Thanks for help.

Answer 1

First - Artem beat me to it but the correct code is:

CREATE function MultiStringToTable (@InStr VARCHAR(255))
RETURNS @TempTab TABLE(id nvarchar(255) not null)
AS
BEGIN
    -- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',');
    DECLARE @SP INT;
    DECLARE @VALEUR VARCHAR(1000);
    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
        SET @SP = PATINDEX('%,%',@INSTR);
        SET @VALEUR = LEFT(@INSTR , @SP - 1);
        SET @INSTR = STUFF(@INSTR, 1, @SP, '');
        INSERT INTO @TempTab(id) VALUES (@VALEUR);
    END;
RETURN;
END;
GO

That said - ^^^ This is going to be horribly slow. For what you are doing you can use STRING_SPLIT.

DECLARE @instr VARCHAR(8000) = 'abc,,xxx,yyy,,z';

SELECT split.* FROM STRING_SPLIT(@instr,',') AS split WHERE  split.[value] > '';

Done. Better yet, you can have any number of commas and they will be treated as one. Note the change below:

DECLARE @instr VARCHAR(8000) = 'abc,,,,xxx,,yyy,,z,w,,,,sss,,';

This returns:

value
----------
abc
xxx
yyy
z
w
sss

To understand the performance difference let's do a quick test.

PRINT CHAR(10)+'STRING_SPLIT'+CHAR(10)+REPLICATE('-',90);
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(8000);

 SELECT      @x = split.[value] 
 FROM        #strings AS s
 CROSS APPLY STRING_SPLIT(s.String,',') AS split
 WHERE  split.[value] > ''

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

PRINT CHAR(10)+'dbo.MultiStringToTable'+CHAR(10)+REPLICATE('-',90);
GO
DECLARE @st DATETIME = GETDATE(), @x VARCHAR(8000);

 SELECT      @x = split.id 
 FROM        #strings AS s
 CROSS APPLY dbo.MultiStringToTable(s.String) AS split
 WHERE  split.id > '';

PRINT DATEDIFF(MS,@st,GETDATE());
GO 3

STRING_SPLIT
--------------------------------------------------------------------------------------
Beginning execution loop
140
184
153
Batch execution completed 3 times.

dbo.MultiStringToTable
--------------------------------------------------------------------------------------
Beginning execution loop
14046
14174
14466
Batch execution completed 3 times.

STRING_SPLIT is about One Hundred times faster and with much less code to boot.

Answer 2

1. wrong parameter name: @InStr - correct
2. missed BEGIN keyword after WHILE

correct version:

CREATE function MultiStringToTable (@InStr VARCHAR(255))
    RETURNS @TempTab TABLE(
   id nvarchar(255) not null
   )
   AS
BEGIN
    -- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',');
    DECLARE @SP INT;
    DECLARE @VALEUR VARCHAR(1000);
    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
        SET @SP = PATINDEX('%,%',@INSTR);
        SET @VALEUR = LEFT(@INSTR , @SP - 1);
        SET @INSTR = STUFF(@INSTR, 1, @SP, '');
        INSERT INTO @TempTab(id) VALUES (@VALEUR);
    END;
RETURN;
end;