Execute function with multiple values in single parameter

Question

I have SQL function which takes parameter as BIGINT like below:

FUNCTION [dbo].[fn_doc]
(
     @DocID         bigint           
)

I want to pass multiple doc ID and want to get results. For which I'm doing like this:

declare @DocID   bigint
SET @DocID = (2949146,2949148,2949149,14016926,14025278,14016928,14016928,14025280,14025280)
select * from [fn_doc] (@DocID)

My question is, how to execute a function with multiple values to single parameter in this case?

All the scenarios are the same tbh. If you need to pass a list of values as a parameter to a function or SP, use a TVP. That's the general advise. Plenty of stuff to be found online if you search for `sql server table valued parameter`. GL! — TT., Oct 31 '16 at 10:40
Hi! Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Since you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Shnugo, Nov 07 '16 at 07:50

score 0 · Answer 1 · edited May 23 '17 at 12:01

This cannot work

declare @DocID   bigint
SET @DocID = (2949146,2949148,2949149,14016926,14025278,14016928,14016928,14025280,14025280)

A variable declared as BIGINT can carry nothing else than a big number...

What you are trying to pass is a comma delimitted string which represents a list of numbers.

There is no built in functionality for this. Neiter the IN-clause, nor any kind of JOIN will do this natively.

You have several choices

Dynamic SQL to create a select statement as string and use EXEC to get the result (something like WHERE docid IN(100,200,300))
Create a table (variable, temp or classic), fill it with your values and use IN or JOIN within your function (This will have issues with parallel actions!)
Create a TYPE to hand in the list like a table
Use some kind of split operation to get a derived table. For this option you might read this answer (section "dynamic IN")

Here is a post with list of approaches

tgr · Answer 2 · 2016-10-31T11:25:42.477

quick and dirty, but should work :-) it's an example for Shnugo's last suggestion

CREATE FUNCTION [dbo].[fn_doc] (@DocID as bigint)
RETURNS bigint
AS
BEGIN
    RETURN @DocID
END

/* your function parameter is a comma seperated list */
DECLARE @Parameter VARCHAR(200)
SET @Parameter = '2949146,2949148,2949149,14016926,14025278,14016928,14016928,14025280,14025280'

/* start and end current parameter */
DECLARE @start int, @end int
SET @start = 0
SET @end = 1

/* current parameter */
DECLARE @p NVARCHAR(200)

WHILE @end > 0
BEGIN
    SET @end = CHARINDEX(',', @Parameter, @start)
    IF @start < @end
       IF ISNUMERIC(SUBSTRING(@Parameter, @start, @end-@start)) = 1 
          SET @p = SUBSTRING(@Parameter, @start, @end-@start)
          PRINT [dbo].[fn_doc] (@p)
    SET @start = @end +1
END

This might be *dirty* but - for sure! - it is not *quick*. – Shnugo Oct 31 '16 at 13:09 — Shnugo, Oct 31 '16 at 13:09

TT. · Answer 3 · 2016-11-01T09:07:48.190

Apparantly you're still not out of the woods yet. So I'll leave you with an example.

Create the TABLE type:

CREATE TYPE dbo.document_ids AS TABLE (
    document_id BIGINT
);
GO

If you can guarantee that only distinct document_id values will be passed as a parameter, you can benefit from defining a primary key on the document_id field:

CREATE TYPE dbo.document_ids AS TABLE (
    document_id BIGINT PRIMARY KEY
);
GO

Create functions dependent on that TABLE type, one scalar-valued and one table-valued:

CREATE FUNCTION dbo.fn_doc_sv (
    @doc_ids dbo.document_ids READONLY
)
RETURNS BIGINT
AS
BEGIN
    RETURN (SELECT SUM(document_id) FROM @doc_ids); 
END
GO

CREATE FUNCTION dbo.fn_doc_tv (
    @doc_ids dbo.document_ids READONLY
)
RETURNS TABLE
AS
    RETURN (SELECT document_id FROM @doc_ids);
GO

Usage of both functions:

DECLARE @doc_ids dbo.document_ids;
INSERT INTO @doc_ids(document_id)VALUES(2949146),(2949148),(2949149),(14016926),(14025278),(14016928),(14016928),(14025280),(14025280);

SELECT dbo.fn_doc_sv(@doc_ids);

SELECT * FROM dbo.fn_doc_tv(@doc_ids);

Hope that makes it clear for you.

Execute function with multiple values in single parameter

3 Answers3