SQL Server : input a string of IDs and Return a string of values

Question

I have a table called tblEventDates with DateID and EventDate columns. I need to provide a stored procedure the param of @DateIDs which is a string of DateIDs separated by a pipe, and return a string of EventDates separated by a comma.

This is what I have tried so far

CREATE PROCEDURE [dbo].[ParseDates]
    @DateIDs VARCHAR(100),
    @ReturnDates VARCHAR(8000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT,
            @DateID INT,
            @D VARCHAR(8000),
            @TmpDate VARCHAR(8000)          

    SET @D = @DateIDs
    SET @i = CHARINDEX('|', @D)

    WHILE @i > 0
    BEGIN
        SET @DateID = CONVERT(INT, SUBSTRING(@D, 1, @i - 1))

        SELECT @TmpDate = EventDate  
        FROM tblEventDates 
        WHERE DateID = @DateID

        SET @ReturnDates = CONCAT(@ReturnDates, ', ', @TmpDate)

        SET @D = SUBSTRING(@D, @i + 1, 9999)
        SET @i = CHARINDEX('|', @D)

        CONTINUE
    END

    IF LEN(@D) > 0
    BEGIN
        SELECT @TmpDate = EventDate  
        FROM tblEventDates 
        WHERE DateID = @DateID

        SET @ReturnDates = CONCAT(@ReturnDates, ', ', @TmpDate)
    END
END
GO

So if @DateIDs = '65|67|69', @ReturnDates should be '01/15/2019, 01/16/2019, 01/17/2019'.

Just a side note. Loops should be avoided as often as possible. If not 2016+, there are far more performant parse/split functions available. — John Cappelletti, Jan 16 '19 at 01:10
John, thanks for the input. Sql is not my area of expertise, so point well taken and appreciated — skrantz, Jan 16 '19 at 03:18
In SQL, it's best of you don't mess around with delimited strings at all. — Zohar Peled, Jan 16 '19 at 07:33

score 0 · Answer 1 · answered Jan 16 '19 at 00:58

Providing you are using SQL Server 2016 or higher, you can leverage the STRING_SPLIT function to turn the delimited @DateIDs string into a table, join that against your date table (which I have called my_date_table with the fields id and date_value), and then concatenate the dates using a trick involving JSON_VALUE and REPLACE (found here):

DROP TABLE IF EXISTS #dates;

SELECT
    CONVERT(VARCHAR(10), my_date_table.date_field, 101) AS date_string
INTO #dates
FROM my_date_table
INNER JOIN (
    SELECT [value]
    FROM STRING_SPLIT(@DateIDs, '|')  
) date_ids
    ON date_ids.[value] = my_date_table.id
;

SET @returnDates = COALESCE(
    JSON_VALUE(
        REPLACE(
            (
                SELECT
                    _ = date_string
                FROM #dates
                FOR JSON PATH
            )
        ,'"},{"_":"',', '),'$[0]._'
    )
, '');

e_i_pi, Thank you for your answer. While your answer works equally as well, John's was easier for me to understand and was achieved with fewer lines of code (and who doesn't like fewer lines). As I am not a SQL expert, I do not know if one is more efficient, or a best practice, so I had to base my choice on what I personally liked best. — skrantz, Jan 16 '19 at 19:50

John Cappelletti · Accepted Answer · 2019-01-16T19:28:07.643

0

There's a brute force option as well

Example

Declare @S varchar(max) = '65|67|69'

Set @S='|'+@S+'|'
Select @S = replace(@S,concat('|',DateID,'|'),'|'+convert(varchar(10),EventDate,101)+'|')
 From  tblEventDates

Select replace(substring(@S,2,len(@S)-2),'|',', ')

Returns

01/15/2019, 01/16/2019, 01/17/2019

edited Jan 16 '19 at 19:28

answered Jan 16 '19 at 01:06

John Cappelletti

79,615
7
44
66

Not sure what the problem is, but when I run the query the result I get is: 03/04/04/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/08/20111/12/2015/07/20109/14/2014/04/03/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/10/2015, 03/04/04/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/08/20111/12/2015/07/20109/14/2014/04/05/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/10/2015, 03/04/04/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/08/20111/12/2015/07/20109/14/2014/09/10/2014/04/07/20109/11/20107/12/2015/01/04/07/20109/12/20107/10/2015 – skrantz Jan 16 '19 at 19:13
@SteveKrantzman Try the updated answer. I forgot to keep the delimiters – John Cappelletti Jan 16 '19 at 19:28
@SteveKrantzman Just in case here is a dbFiddle https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=6ba344f80915f8b45b9aa1ec8e6d13a6 – John Cappelletti Jan 16 '19 at 19:36

SQL Server : input a string of IDs and Return a string of values

2 Answers2