Passing Multiple Values in Single Parameter

How to pass multiple values to single parameter in stored procedure

This can not be done easily. There's no way to make an NVARCHAR parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.

Splitting up can be done using string functions. Add every part to a temporary table. Pseudo-code for this could be:

CREATE TABLE #TempTable (ID INT)
WHILE LEN(@PortfolioID) > 0
BEGIN
    IF NOT <@PortfolioID contains Comma>
    BEGIN
        INSERT INTO #TempTable VALUES CAST(@PortfolioID as INT)
        SET @PortfolioID = ''
    END ELSE
    BEGIN
         INSERT INTO #Temptable VALUES CAST(<Part until next comma> AS INT)
         SET @PortfolioID = <Everything after the next comma>
    END
END

Then, change your condition to

WHERE PortfolioId IN (SELECT ID FROM #TempTable)

EDIT
You may be interested in the documentation for multi value parameters in SSRS, which states:

You can define a multivalue parameter for any report parameter that
you create. However, if you want to pass multiple parameter values
back to a data source by using the query, the following requirements
must be satisfied:

The data source must be SQL Server, Oracle, Analysis Services, SAP BI
NetWeaver, or Hyperion Essbase.

The data source cannot be a stored procedure. Reporting Services does
not support passing a multivalue parameter array to a stored
procedure.

The query must use an IN clause to specify the parameter.

This I found here.

Correct way to pass multiple values for same parameter name in GET request

Indeed, there is no defined standard. To support that information, have a look at wikipedia, in the Query String chapter. There is the following comment:

While there is no definitive standard, most web frameworks allow
multiple values to be associated with a single field.[3][4]

Furthermore, when you take a look at the RFC 3986, in section 3.4 Query, there is no definition for parameters with multiple values.

Most applications use the first option you have shown: http://server/action?id=a&id=b. To support that information, take a look at this Stackoverflow link, and this MSDN link regarding ASP.NET applications, which use the same standard for parameters with multiple values.

However, since you are developing the APIs, I suggest you to do what is the easiest for you, since the caller of the API will not have much trouble creating the query string.

How to pass multiple values to one parameter

http://knexjs.org/#Builder-whereIn

query.whereIn('category.id', [1, 2, 3]);

Passing multiple values in single parameter

(Your function wouldn't be created. RETURN after END is syntactical nonsense.)

A function with a VARIADIC parameter does exactly what you ask for:

CREATE OR REPLACE FUNCTION test_function(date, date, VARIADIC varchar[])
  RETURNS SETOF integer
  LANGUAGE sql AS
$func$
SELECT col1
FROM   test_table
WHERE  col3 > $1
AND    col4 < $2
AND    col2 = ANY($3)
$func$;

db<>fiddle here - demo with additional parameters

_{Old sqlfiddle}

Call (as desired):

SELECT * FROM test_function('data1', 'data2', 'data3');

Using a simple SQL function, plpgsql is not required for the simple example. But VARIADIC works for plpgsql functions, too.

Using RETURNS SETOF integer since this can obviously return multiple rows.

Details:

• Pass multiple values in single parameter
• Return rows matching elements of input array in plpgsql function
• VARIADIC parameter must be the last input parameter
• Return rows matching elements of input array in plpgsql function

Pass multiple values in single parameter

VARIADIC

Like @mu provided, VARIADIC is your friend. One more important detail:

You can also call a function using a VARIADIC parameter with an array type directly. Add the key word VARIADIC in the function call:

SELECT * FROM  f_test(VARIADIC '{1, 2, 3}'::int[]);

is equivalent to:

SELECT * FROM  f_test(1, 2, 3);

Other advice

In Postgres 9.1 or later right() with a negative length is faster and simpler to trim leading characters from a string:

right(j.status, -2)

is equivalent to:

substring(j.status, 3, char_length(jobs.status))

You have j."DeleteFlag" as well as j.DeleteFlag (without double quotes) in your query. This is probably incorrect. See:

• PostgreSQL Error: Relation already exists

"DeleteFlag" = '0' indicates another problem. Unlike other RDBMS, Postgres properly supports the boolean data type. If the flag holds boolean data (true / false / NULL) use the boolean type. A character type like text would be inappropriate / inefficient.

Proper function

You don't need PL/pgSQL here. You can use a simpler SQL function:

CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
  RETURNS TABLE (id int, reference int, job_title text, status text)
  LANGUAGE sql AS
$func$
   SELECT j.id, j.reference, j.job_title
        , ltrim(right(j.status, -2)) AS status
   FROM   company c
   JOIN   job     j USING (id)
   WHERE  c.active
   AND    NOT c.delete_flag
   AND    NOT j.delete_flag
   AND   (j.id = ANY($1) OR '{-1}'::int[] = $1)
   ORDER  BY j.job_title
$func$;

db<>fiddle here

_{Old sqlfiddle}

Passing multiple values for a single parameter in Reporting Services

Although John Sansom's solution works, there's another way to do this, without having to use a potentially inefficient scalar valued UDF.
In the SSRS report, on the parameters tab of the query definition, set the parameter value to

=join(Parameters!<your param name>.Value,",")

In your query, you can then reference the value like so:

where yourColumn in (@<your param name>)

---

Related Topics