Nrthugu

I need to return records that I send to a stored procedure in a comma-separated string - like this:

@PMID = 29573145,24106086,20513766,24326307

I have a stored procedure that pulls records such as

SELECT 

    data, 

    PMID  

FROM

    [dbo].[ADMIN_Publication_JSON] 

WHERE 

    PMID IN (SELECT DATA FROM dbo.Split(@PMID, ',')) 

The problem that I am having is that the return record set is random and I need it precise because my end user could change the order and the records need to be displayed in that order which would change the order in the comma string. Is this possible or do I need to totally change the way I pull the data? Thanks

You can use a window function like

Select T1.data, 

       T1.PMID  

FROM [dbo].[ADMIN_Publication_JSON] T1 INNER JOIN

     (SELECT Data,

             ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN

      FROMdbo.Split(@PMID,',')

     ) T2 ON  T1.PMID = T2.Data 

ORDER BY T2.RN;

Here is a little sample:

CREATE TABLE T(

  ID INT,

  SomeValue VARCHAR(45)

);



INSERT INTO T VALUES

(1, 'One'),

(2, 'Two'),

(3, 'Three'),

(4, 'Four'),

(5, 'Five');



DECLARE @IDs VARCHAR(200) = '3,5,2';



SELECT T.*

FROM T INNER JOIN 

       (SELECT Value,

               ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) Seq

       FROM STRING_SPLIT(@Ids, ',') --instead of your function

       ) TT

       ON T.ID = TT.Value

ORDER BY TT.Seq;

Live Demo

Split method does not sort the Data column that means simple join with its result can do the trick. You don't need ROW_NUMBER() or any sorting effort here. Have a temp table store Splits result and LEFT JOIN the two. This works for me.

CREATE TABLE #Input (PMID varchar(10))

INSERT INTO #Input SELECT Data FROM dbo.Split(@PMID, ',')



SELECT 

   jsn.*

FROM 

   #Input spl INNER JOIN ADMIN_Publication_JSON jsn on spl.PMID = jsn.PMID

Output: Returns the set in the order passed in @PMID

I'm sorry to say but the currently accepted answer (by Sami) is wrong.

The problem with this answer is that it use ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) to get the order of the items in the comma delimited string, but since the order by is done on select null, what actually happens is that the row_number will assign the numbers in an arbitrary order - that may or may not match the order of the strings in the source string.

If your split UDF returns a table with two columns, where one contains the substring and the other contains it's index, like Jeff Moden's DelimitedSplit8K, then simply use the ItemNumber (or equivalent) column for the order by. If it only returns a single column containing the substrings, you can use this a nice trick I've learned from Aaron Bertrand's Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions - it will guarantee to return the correct order of the substrings as long as they are unique.

A simple change on Sami's answer will give you correct results as long as the substrings are unique within the comma delimited string - Instead of ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), use CHARINDEX(',' + Value + ',', ',' + @Ids + ','), which will return the index of each substring inside the comma delimited string:

CREATE TABLE T(

  ID INT,

  SomeValue VARCHAR(45)

);



INSERT INTO T VALUES

(1, 'One'),

(2, 'Two'),

(3, 'Three'),

(4, 'Four'),

(5, 'Five');



DECLARE @IDs VARCHAR(200) = '3,5,2';



SELECT T.*

FROM T 

INNER JOIN 

       (SELECT Value,

               CHARINDEX(',' + Value + ',', ',' + @Ids + ',') AS Seq

       FROM STRING_SPLIT(@Ids, ',') --instead of your function

       ) TT

       ON T.ID = TT.Value

ORDER BY TT.Seq;