0

So, in short, I need to connect from data to a SQL query. I had not trouble doing this with a fixed query. I however, want that query to update based on values in certain cell. Since the person using it might not be familiar with SQL, Excel, or VBA, I was tasked with making the data update simply with the Excel refresh button. Anyway, super simplifying, I want to run a query like this:

SELECT 
    column1, column2, column3
FROM 
    table
WHERE 
    important_date BETWEEN [A1] and [A2]
    AND thing1 IN [list from A3]
    AND thing2 = [A4]

I saw this thread here: Excel: Use a cell value as a parameter for a SQL query and it did not seem to answer my question for 2 reason:

  1. One work-around seemed to require that the table itself be pulled into excel. The table in question would contain millions of rows, making his infeasible.

  2. Some suggested a question mark (?) workaround, but entering ? for dates, lists, or strings simply outputted errors.

Given this, is there a possible way for me to do this? If so, how? and finally, if I can, how would I go about dealing with a blank list? For instance, if someone does not list anything in A3, I'd like the query to simply ignore the "AND thing1 in [list from A3] part of the code.

Thank you,

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Minerva
  • 1
  • Could u please tag this questions with the technologies you are using? This is simply not possible with just SQL as SQL only has 4 statements, (SELECT, INSERT, UPDATE, DELETE). None of them have anything to do with what you are trying to accomplish. – Jamie Marshall Jul 03 '18 at 02:03
  • It's for SQL Server Management Studio 17. Not sure how to add tags – Minerva Jul 03 '18 at 14:58
  • i'm not sure this is a programming question. What your asking is more of a real broad question on architecture and what technologies to use to do this from excel as the front end. That takes a reasonable amount of programming skill with at least VBA, not just tsql. If I were you i'd look into powerquery. Powerquery by MS comes standard with excel from 2016 onward and has the ability to maintain connections with SQL server. I use power query to do stuff just like this at our office. – Jamie Marshall Jul 03 '18 at 15:41
  • How do I create a power query? I've seen it mentioned but I've never seen someone show where that feature is – Minerva Jul 03 '18 at 16:52

0 Answers0