# Pass Parameter from Excel Cell to SQL Query



## martinshort (Nov 9, 2020)

Hi

I have a query in SQL, an excerpt of which might look something like


```
DECLARE @myDate DATETIME
SET @myDate = '2020-10-31'
SELECT * FROM myTable WHERE myDate <= @myDate + 1
```

I want to populate a cell in Excel (e.g. A1) named range "myCell" that will feed through to the SQL variable instead of setting it in the SQL code.

I've not really worked with parameters in Power Query before and a lot of searching has left me with solutions that are similar/cause syntax errors in the M code, and a great big headache.

Can someone point me in the right direction please?

Thanks


----------



## smozgur (Nov 9, 2020)

We know that we can get that named cell value into Power Query like this:

`myDate = Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1]`

And then you just need the SQL SELECT statement, right? Perhaps following might help.
Assuming `myCell` contains a date value in Excel, I think following should be a sample to create the query, and use with `Sql.Database` function.


```
let
    myDate = Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1],
    nextDay = Date.AddDays(Date.From(myDate), 1),
    sqlDate = Date.ToText(nextDay, "yyyy-MM-dd"),

    Source = 
        Sql.Database(
            "server", 
            "database", 
            [Query="SELECT * FROM myTable WHERE myDate <= " & sqlDate])

in
    Source
```


----------



## martinshort (Nov 10, 2020)

Thanks Smozgur. (Love the BatCoder handle)

I was so close!

For various reasons that I won't bore you with,  I am unable functions or procs on this database.  Not in a reasonable timeframe anyway, hence my vary specific request.  Procs would be my preferred option any day!


----------



## martinshort (Nov 10, 2020)

Alas, it still has an error.  Power Query has automatically split it into three steps:

myDate

```
= Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1]
```

sqlDate

```
= Date.ToText(myDate, "yyyy-MM-dd")
```

and the third is the actual SQL.

sqlDate fails.  The error message is:



> Expression.Error: We cannot convert the value #datetime(2020, 10, 31, 0, 0, 0) to type Date.
> Details:
> Value=31/10/2020 00:00:00
> Type=Type



_*By the way, I removed the nextDay step as this is covered in the main body of the SQL._


----------



## Norie (Nov 10, 2020)

Have you tried using #date instead of #datetime?


----------



## martinshort (Nov 10, 2020)

Morning Norie - hope you're well and not coughing!

Maybe I'm being a bit thick, but I can't see where I specific #datetime in the code above?


----------



## Norie (Nov 10, 2020)

Oops, sorry - I was looking at the error message from PowerQuery.

What format is your Excel date in?


----------



## martinshort (Nov 10, 2020)

It needs to be 'YYYY-MM-DD' - ie internal SQL date format.  It then gets passed to a datetime parameter inside the SQL where the parameter is dealt with in different ways, dependent on the date. 
(Not that I'm interested in explaining the workings of one extremely complex bit of SQL  )

I'm finding it a bit difficult to believe that passing a date from an Excel cell to @AS_AT_DATE, which is the real date parameter in the SQL code, could be so not straightforward.  I thought that Smozgur's solution would crack it, but there's still one small piece of the puzzle missing.  I've found nothing on t'internet that explains how to do this.


----------



## Norie (Nov 10, 2020)

What happens if you convert the date in Excel to a text value with the required format using TEXT.

The reason I suggest that is because it seems, to me anyway, that Power Query is seeing a time part in the value you are passing to it.


----------



## smozgur (Nov 10, 2020)

martinshort said:


> By the way, I removed the nextDay step as this is covered in the main body of the SQL


You also removed Date.From that handles that error. 
Use this for sqlDate instead:


```
sqlDate = Date.ToText(Date.From(myDate), "yyyy-MM-dd")
```


----------



## martinshort (Nov 9, 2020)

Hi

I have a query in SQL, an excerpt of which might look something like


```
DECLARE @myDate DATETIME
SET @myDate = '2020-10-31'
SELECT * FROM myTable WHERE myDate <= @myDate + 1
```

I want to populate a cell in Excel (e.g. A1) named range "myCell" that will feed through to the SQL variable instead of setting it in the SQL code.

I've not really worked with parameters in Power Query before and a lot of searching has left me with solutions that are similar/cause syntax errors in the M code, and a great big headache.

Can someone point me in the right direction please?

Thanks


----------



## martinshort (Nov 10, 2020)

Ah ha!  Thanks Smozgur.

Thanks to both of you.

I've finally managed to get it to work and I couldn't have done it without your collective help. _ (How does one mark up all your comments as the solution?)_

I used the Text option in Excel. then ditched the parameters, sub queries etc. and loaded it as:


```
= Sql.Database("SERVERNAME",
               "REPORTING_DB", 
               [Query="DROP TABLE IF EXISTS #TRAN_ALL;#(lf)#(tab)DECLARE @As_At_Date    DATETIME#(lf)SET @As_At_Date = '" & Excel.CurrentWorkbook(){[Name="AS_AT_DATE"]}[Content]{0}[Column1] & "' #(lf)SET @Status= 'ALL' ..."
```

A few more parameters to add and I've got my working interim model...


----------



## smozgur (Nov 10, 2020)

Good to hear it is solved.



martinshort said:


> How does one mark up all your comments as the solution?



Only one post can be marked as solution. 
*You can change the solution post by clicking on another one that helped you better.*
And you can always Like the posts that you like or helps you.


----------

