How to pass multiple values in Databricks parameter values in Databricks SQL end point?

Question

Ex 1:- select * from test_db.Emp where empid in (1,2)

Result:- | EmpID | EmpName|
| 1 | Raja |
| 2 | Rani|

Ex 2:- select * from test_db.Emp where empid in ({{EmpId}})

Parameter Passing values EmpId:- 1,2

Result:- | EmpID | EmpName|

0 records

I am not getting result when we are passing 1,2 for EmpId Parameter. but, I am getting values while I passing static values and getting values while I passing single value as parameter.

Ex 3:- select * from test_db.Emp where empid in ({{EmpId}})

Parameter Passing values EmpId:- 1

Result:- | EmpID | EmpName|
| 1 | Raja |

Answer 1

This is because by default parameter is set to Text type. When value is given as 1,2 in text type, it searches for rows with id = 1,2. No records have id=1,2. Thus, zero record is displayed. Supported types for query parameter are Text, Number, Date, Date and Time, Date and Time (with Seconds), Dropdown List, and Query Based Dropdown List. You can use dropdown type in this case instead of text type. To change the type, follow the below steps.

• Click on the gear icon near the parameter

• Then change the parameter type to Dropdown list

now, when values are passed, it is executed without any error.

Reference: Query parameters - Azure Databricks - Databricks SQL | Microsoft Learn