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.