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.
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
How to pass multiple values to one parameter
http://knexjs.org/#Builder-whereIn
query.whereIn('category.id', [1, 2, 3]);
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!.Value,",")
In your query, you can then reference the value like so:
where yourColumn in (@)
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( AS INT)
SET @PortfolioID =
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.
Related Topics
How to Query a Value in SQL Server Xml Column
SQL Statement Error: "Column .. Does Not Exist"
Get Day of Week in SQL Server 2005/2008
What Are Covering Indexes and Covered Queries in SQL Server
SQL Query: Delete All Records from the Table Except Latest N
MySQL, Better to Insert Null or Empty String
Convert Xml to Table SQL Server
How to Include a PHP Variable Inside a MySQL Statement
Generating a Random & Unique 8 Character String Using MySQL
Hive Select Count(*) Non Null Returns Higher Value Than Select Count(*)
Check If MySQL Table Exists Without Using "Select From" Syntax
Filter Table Before Applying Left Join
How to Use Table Variable in a Dynamic SQL Statement
MySQL Query to Select Data from Last Week