CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-function
Tom
Tom

Reputation: 1373

Insert quotation marks to each word in sql comma separated string and use in Function SQL

I have a string like this:

@Values VARCHAR(1000)
SET @Values = 'one, two, three'

I want to use this string in a WHERE clause with an IN operator.

For that I'm using following table valued function. 

ALTER FUNCTION [dbo].[FN_RPT_CommaSeperated] 
    (@StringInput VARCHAR(8000), 
     @Delimiter NVARCHAR(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
    DECLARE @String VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String = LEFT(@StringInput, 
                           ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                           LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ([String])
        VALUES ('''' + @String + '''')
    END

    RETURN
END

This function is returning values in expected way. But still my stored procedure not giving data as expected.

In the stored procedure I'm using like this:

  WHERE
      TD.CaseId IN (SELECT * FROM [FN_RPT_CommaSeperated] (@Values,','))

Any way when I hard code the values like this in the stored procedure, it is returning the expected output.

WHERE
    TD.CaseId IN ('one', 'two', 'three')

Any ideas how to get the output using the function?

Upvotes: 0

Views: 1327

Answers (4)

Venkataraman R
Venkataraman R

Reputation: 12969

There is already a Table Valued function comes in-built in SQL Server, since SQL Server 2016. String_Split

DECLARE @Values VARCHAR(1000)
SET @Values = 'one, two, three'
SELECT value FROM STRING_SPLIT(@Values, ',');

You can do something like below:

WHERE
    TD.CaseId IN (SELECT value FROM STRING_SPLIT(@Values));

Upvotes: 2

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

You need to small change in your funtion

ALTER FUNCTION [dbo].[FN_RPT_CommaSeperated] 
    (@StringInput VARCHAR(8000), 
     @Delimiter NVARCHAR(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
    DECLARE @String VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String = LEFT(@StringInput, 
                           ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                           LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ([String])
        VALUES ('' + @String + '')
    END

    RETURN
END

use VALUES ('' + @String + '') instead of VALUES ('''' + @String + '''')

Upvotes: 0

Tom
Tom

Reputation: 1373

I changed a function little bit and WHERE clause in SP to get the expected output. That is working fine.

ALTER FUNCTION [dbo].[FN_RPT_CommaSeperated] 
(@StringInput VARCHAR(8000), 
 @Delimiter NVARCHAR(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
    SET @String = LEFT(@StringInput, 
                       ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                       LEN(@StringInput)))
    SET @StringInput = SUBSTRING(@StringInput,
                                 ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                 LEN(@StringInput)) + 1, LEN(@StringInput))

    **INSERT INTO @OutputTable ([String])
    VALUES (@String)**
END

RETURN
END

In the Stored Procedure changed like this.

WHERE 
**exists
(select * from [FN_RPT_CommaSeperated_Kiosk] (@KioskId,',') where [String] = TD.CaseId)**

Upvotes: 0

TomC
TomC

Reputation: 2814

You are close, but since you now have a table, you can use something like:

WHERE exists
   (select * from [FN_RPT_CommaSeperated] (@Values,',') where [string]=TD.CaseId)

Upvotes: 1

Related Questions