Stored procedure to find the value of a field in a list of values in an input string

Andrii Shylin 221 Reputation points
2021-08-17T14:21:08.16+00:00

I need to write stored procedure to find the value of a field in a list of values in an input string.
For example, I have a string containing a comma separated list of ids, and I need to find a list of documents whose id is in this list

ALTER FUNCTION [dbo].[ufGetAssociatedDocuments]
(
    @docId VARCHAR(200)      --input string '123, 546, 657, 785'
) 
RETURNS @AssociatedFilesInfo TABLE (
    DocumentId VARCHAR(20),
    FileName VARCHAR(80),
    ChangeDate DATETIME,
    FileType VARCHAR(30),
    PartnerAccess SMALLINT)
AS
BEGIN

    INSERT INTO @AssociatedFilesInfo
    SELECT TOP(1)
        ff.doc_id,--Varchar(20)
        ff.file_name, 
        ff.change_date,
        ft.description, 
        ff.access_cust
    FROM dbo.folder ff
    WHERE
        --ff.doc_id IN @docIds  --how to write this statement
            --docIds like '%' + ff.doc_id+ '%';

    RETURN;
END
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,201 Reputation points
    2021-08-18T01:29:30.7+00:00

    Hi @Andrii Shylin ,

    Welcome to Microsoft Q&A!

    What is your version of SQL Server?

    If it is SQL server 2016 and later, you could use STRING_SPLIT() mentioned by other experts.

    If it is SQL Server 2014 and earlier, you could also refer below user-defined function [dbo].[SplitString] ,refer to this forum.

    CREATE FUNCTION [dbo].[SplitString]  
    (  
        @List NVARCHAR(MAX),  
        @Delim VARCHAR(255)  
    )  
    RETURNS TABLE  
    AS  
        RETURN ( SELECT [Value] FROM   
          (   
            SELECT   
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
              FROM sys.all_objects) AS x  
              WHERE Number <= LEN(@List)  
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
          ) AS y  
        );  
    

    Then you could update your function like below:

    ALTER FUNCTION [dbo].[ufGetAssociatedDocuments]  
     (  
         @docId VARCHAR(200)      --input string '123, 546, 657, 785'  
     )   
     RETURNS @AssociatedFilesInfo TABLE (  
         DocumentId VARCHAR(20),  
         FileName VARCHAR(80),  
         ChangeDate DATETIME,  
         FileType VARCHAR(30),  
         PartnerAccess SMALLINT)  
     AS  
     BEGIN  
          
         INSERT INTO @AssociatedFilesInfo  
         SELECT TOP(1)  
             ff.doc_id,--Varchar(20)  
             ff.file_name,   
             ff.change_date,  
             ff.description,   
             ff.access_cust  
         FROM dbo.folder ff  
         WHERE ff.doc_id IN (SELECT value FROM [dbo].[SplitString](@docId, ','))  
          
         RETURN;  
     END  
    

    Finally you could call this function like below:

    select * from [dbo].[ufGetAssociatedDocuments]('123, 546, 657, 785')  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Viorel 118.2K Reputation points
    2021-08-17T14:37:29.787+00:00

    Probably the simplest approach is using STRING_SPLIT function. If it is not available, then consider this method too:

    where ff.doc_id in ( select value from openjson(concat('[', @docId, ']')))

    It assumes that the list consists of numbers.

    1 person found this answer helpful.
    0 comments No comments

  2. Guoxiong 8,206 Reputation points
    2021-08-17T15:24:56.723+00:00

    As @Viorel mentioned, you can use the system function STRING_SPLIT() or OPENJSON() if you SQL server is 2016 or later:

     INSERT INTO @AssociatedFilesInfo  
        SELECT TOP(1)  
             ff.doc_id,--Varchar(20)  
             ff.file_name,   
             ff.change_date,  
             ft.description,   
             ff.access_cust  
        FROM dbo.folder ff  
     WHERE ff.doc_id IN (SELECT value FROM STRING_SPLIT(@docIds, ','))  
    

    If your SQL server is 2014 or previous version, you may try to use the dynamic query if the list of doc_id consists the numbers:

    	DECLARE @sql varchar(max) = '';  
    	SET @sql = '  
    	SELECT TOP(1)  
    		ff.doc_id,--Varchar(20)  
    		ff.file_name,   
    		ff.change_date,  
    		ft.description,   
    		ff.access_cust  
    		FROM dbo.folder ff  
    		WHERE ff.doc_id IN (' + @docId + ')  
    	  ';  
      
    	INSERT INTO @AssociatedFilesInfo  
    	EXEC( @sql)  
    
    1 person found this answer helpful.

  3. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-17T21:31:16.773+00:00

    I have a short article on my website that discusses possible option (of which some have already been mentioned above): https://www.sommarskog.se/arrays-in-sql.html.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.