Pass Parameter from Excel Cell to SQL Query

martinshort

Board Regular
Joined
Feb 19, 2008
Messages
204
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
  6. 2003 or older
Platform
  1. Windows
Hi

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

SQL:
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 :)
 
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:

Power Query:
= 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... :)
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Good to hear it is solved.

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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top