How to pass multiple parameter passing without function?

Question

I had written the function to pass multiple parameters in to query in where clause but now I am facing performance issue.

I need to avoid the function to pass multiple parameter.

CREATE FUNCTION [dbo].[UTILfn_Split] (@String NVARCHAR(max), @Delimiter VARCHAR(10))
RETURNS @ValueTable TABLE ([Value] NVARCHAR(max))

BEGIN
    DECLARE @NextString NVARCHAR(max)
    DECLARE @Pos INT
    DECLARE @NextPos INT
    DECLARE @CommaCheck NVARCHAR(1)

    --Initialize  
    SET @NextString = ''
    SET @CommaCheck = right(@String, 1)
    --Check for trailing Comma, if not exists, INSERT  
    --if (@CommaCheck <> @Delimiter )  
    SET @String = @String + @Delimiter
    --Get position of first Comma  
    SET @Pos = charindex(@Delimiter, @String)
    SET @NextPos = 1

    --Loop while there is still a comma in the String of levels  
    WHILE (@pos <> 0)
    BEGIN
        SET @NextString = substring(@String, 1, @Pos - 1)

        INSERT INTO @ValueTable ([Value])
        VALUES (@NextString)

        SET @String = substring(@String, @pos + 1, len(@String))
        SET @NextPos = @Pos
        SET @pos = charindex(@Delimiter, @String)
    END

    RETURN
END

Currently I am using function to pass multiple parameters like below ,but I need to improve my query performance avoiding the function in the query. Is there any way to pass multiple parameters without function?

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?view=sql-server-2017 — gotqn, Oct 10 '19 at 06:33
Why reinvent the wheel? [STRING_SPLIT (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) — AlwaysLearning, Oct 10 '19 at 06:37
If you are passing an array of some elements, then use the standard string_split function, but if you are passing several different parameters of different types, why don't you pass an XML string ?. SQL Server has easy functions to encode and decode an XML string. — Marc Guillot, Oct 10 '19 at 06:39
@AlwaysLearning `STRING_SPLIT()` needs v2016+... Besides: If you have v2016+, it is better to use a [JSON-hack (read both update sections in my answer)](https://stackoverflow.com/a/38274439/5089204). This will be position-safe and - if needed - even type-safe... And with a version not capable of JSON I'd prefer the XML approach (also in this answer). — Shnugo, Oct 10 '19 at 07:14

How to pass multiple parameter passing without function?

0 Answers0