I am still receiving an error after following the links instructions.
I am trying to do this using the Value.NativeQuery route and am receiving the following conversion error.
DataSource.Error: Microsoft SQL: Conversion failed when converting the nvarchar value '('MyDataStringHere)' to data type int. Details: DataSourceKind=SQL DataSourcePath=sysqlwh1;PPI Message=Conversion failed when converting the nvarchar value '('MyDataStringHere')' to data type int. Number=245 Class=16
The column I am using the parameter against is data type: int. I am not sure if this is the cause of the error.
My WHERE clause within the NativeQuery looks like this:
WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (@Parameter) )
When I manually run this query in SSMS with my copied parameter I have no issues with the data type as either an int value or a text value in the WHERE clause.
MyParameters = "1,2",
Source = Sql.Database("MyServer", "MyDatabase"),
Query = Value.NativeQuery(Source,"
-- here is SQL body WHERE (T3.INTCOLUMN_1 = 1) AND ( T3.INTCOLUMN_2 IN (" & MyParameters & ") ) ")
With parameter you pass into the query string as "'1,2'". Perhaps it could be split somehow, not sure.