Excel parameters from SQL Query from ODBC "Error - No Value given for one or more required parameters when using question mark `WHERE XXX = ?`

Question

I have an Excel file with an SQLOLEDB connection to a MS SQL server,

When I do this with an Oracle link I can simply put WHERE XXX = ? in my query to prompt for values in Excel, however doing it with MSSQL I am getting the following error:

No Value given for one or more required parameters

When trying to parameterise a basic excel query from a value to ?

If i use values like this I get results:

SELECT * 
FROM srv.stats
WHERE srv.stats.[year] = '2016'
AND srv.stats.[quarter] = '1'

When I add in the parameter ? I get the above error.

SELECT * 
FROM srv.stats
WHERE srv.stats.[year] = ?
AND srv.stats.[quarter] = ?

I want to be able to do this without using VB.

Answer 1

Since MS SQL sources from ODBC connections don't inherently allow you to use the WHERE xxx = ? code in your SQL query (unlike Oracle connections) you need to spoof Excel to allow parameters from a MS SQL data source by building a Microsoft Query and then overwriting it.

Open a new Excel file and go to the Data tab, choose the From Other Sources drop down and select From Microsoft Query.

The Choose Data Source window will appear. Choose an existing datasource or set up a new connection to your server by selecting <New Data Source> and click OK.

Once done you will see the query wizard window open to select tables and columns, as you're going to be adding your own SQL query later just select one table that is in your query and add it to the Columns in your query: section by using the > button. For the next 2 windows just click Next and then finally Finish.

You will then be prompted to select how you want to view the data in the Import Data window, first of all click the Properties... button.

And then the Definition tab, in the Command text: box you will have a SELECT statement, below there you will need to add WHERE clauses for the amount you have in your actual query. These need to be added in the format of:

WHERE 1 = ?
AND 2 = ?
AND 3 = ?

Once this is done click OK to go back to the Import Data window and select your output type; Table, PivotTable report or PivotChart and PivotTable Report depending on what how you want to display your data.

You will then be prompted to enter a value for each parameter. If you are getting these values from Cells choose the location of them now in the order you will be putting in your actual parameters. When you have entered your parameter sources go back to the Data tab and click connections and then into the definition tab.

Now in the Command text: box paste in your actual SQL query with your parameters in the WHERE clause = ? and in the same order your defined the sources and click OK, Your data should now populate as it usually does with your parameters being used.

Answer 2

There is no way to prompt for inputs directly in SQL Server in the same way that you can in Access for example. You can make the values that are passed into queries like this.

DECLARE @year SMALLINT
SET @year = 2016

DECLARE @quarter TINYINT
SET @quarter = 1

SELECT * 
FROM srv.stats
WHERE srv.stats.[year] = @year
  AND srv.stats.[quarter] = @quarter;

You then just need to find a way that suits your solution (Excel?) to pass these. This might, for example, take the form of a stored procedure.

CREATE PROCEDURE testProcedure @year SMALLINT, @quarter TINYINT
AS
    SELECT * 
    FROM srv.stats
    WHERE srv.stats.[year] = @year
      AND srv.stats.[quarter] = @quarter
GO;