Forum Discussion
Parameterized Sql Statement: Expression.Error: This native database query isn't currently supported
That could be
... pParText = fParameter(ParameterTable,From), Source = Sql.Database(Server, Database, [Query = "CALL SALES_BY_CATEGORY_1234 ( '51', " & pParText & " '2020-12-31','',......)"]), nextStep, ...Another way is as here Chris Webb's BI Blog: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI Chris Webb's BI Blog (crossjoin.co.uk) but in general they are not too far from each other.
One of ways is like
prevStep,
Source = Sql.Database(Server, Database,
[Query = "
declare
@pDate date = ' "& myParameter01asText & "';
WITH
......
SELECT something
FROM somewhere
WHERE ID >= " & myParameter02asText & "
ORDER BY some ASC
"]),
nextStepbut with this yes, query won't be folded and it's better max logic to put into SQL query.
If don't use SQL query and do M-script in right order, query folding mechanism will work and actually sql query will be generated in background automatically.
Thank you for the quick reply, please note I'm not really familiar with M code and don't quite understand your solution. I will add some more detail about the issue I'm facing.
My SQL Query looks like this:
CALL SALES_BY_CATEGORY_1234 ( '51','2020-01-01','2020-12-31','','en','dd/MM/yyyy','2020-03-01','2020-12-01','Product','all','all','all','all','','all','','TPHX16','all','all','all','Regular','All','No','No','No','No','No','00208 ','All','202101','Detailed','all','all','all','Customer Salesman','all','Default')Also in terms of the parameterized SQL statement, I have created a table (2 columns, 1st column parameter "identifier" 2nd column, the value I want as input) which a user can input (type), the value which will be the input for the SQL statement. I then created a function with power query that would do this.
Example:
CALL SALES_BY_CATEGORY_1234 ( '51',fParameter(ParameterTable,From),'2020-12-31','',......)where ParameterTable is the name of the table where I have my input and From is the parameter "identifier", which lets the function know which value to place into the query.
You said that your solution was one of a few, is there something that can align more towards what I'm trying to achieve?
Thank you
That could be
... pParText = fParameter(ParameterTable,From), Source = Sql.Database(Server, Database, [Query = "CALL SALES_BY_CATEGORY_1234 ( '51', " & pParText & " '2020-12-31','',......)"]), nextStep, ...Another way is as here Chris Webb's BI Blog: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI Chris Webb's BI Blog (crossjoin.co.uk) but in general they are not too far from each other.
I'll accept this as the best response and start a new thread about the new issue I'm having
cl27274 Thank you. Didn't find your new question so far.
I've tried to do this, unfortunately, I'm getting a new error.
DataSource.Error: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) Details: DataSourceKind=SQL DataSourcePath= (some server name) Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ErrorCode=-2146232060 Number=10054 Class=20Normally, I connect to the SQL database through an ODBC connection. Would this change anything to the code?
I also tried using Odbc.Query, and Odbc.Datasource as alternatives to Sql.Database, but it didn't help