1

I'm trying to pull data from an SQL Server database into Excel 2016 via ODBC / Microsoft Query (built in to Excel).

I'd like to have a dropdown of companies, where after a company is selected from the dropdown, all contacts for that company (from a separate contacts table) are displayed.

From what I've learned so far I need to use :field or @field to indicate that the user chooses - something like:

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))

But Excel / MS Query returns:

"Must declare the scalar variable "@CompanyName". Statement(s) could not be prepared."

Can anyone advise on how I declare the scalar variable? Assuming that's even what I'm actually needing to do here. Many thanks.

0

Have your declaration at the top.. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

DECLARE @CompanyName VARCHAR(200);
SET @CompanyName = 'CompanyName';

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))
  • 2
    That would work in SSMS but he's trying to provide the filter from an option chosen in Excel. – Paul Dec 15 '17 at 11:35
  • Yeah it actually might not even be something you can do... I just assumed it was possible. If I omit the 'where' clause then I'll get the data with searchable / filterable columns anyway, but it's not being pulled live from the database. – Sam Dec 15 '17 at 13:15
0

In my mind there are a few ways you can achieve this, none of which are simple solutions. Which ever way you approach this, I think you will need to make two passes in to the database:

1) to get a list of current company names

2) to get all the contacts associated with chosen company.

First up you need to decide where/how you are going to store your company names. You could either store them in a local table, and provide a lookup via the CompanyName column using a Data Validation list option. This will mean that only the names in the list can be selected. Good for data integrity.

Or, if you don't want to store a local table then you will need to execute some vba code to connect to the database and download the company names. Iterate through the list of names and deliver the options to the user in whatever format you like.

Alternatively, you could manually write a list of names, but this will require additional maintenance when new company names are added (not ideal). Or, if you are really brave/mad, let the user write the company name in (I don't recommend this!!!!!).

Back to your original question, if you are using MS Query you should be able to parameterise your query in the query editor (if I remember correctly?!). Parameterise it such that the parameter value is taken from the cell where the user selects the company name. MS Query should do the rest for you... provided the query is parametrised client-side (i.e. in excel). Alternatively, you could once again go back to vba and dynamically create the query string in the code, passing in the CompanyName to replace @CompanyName and executing a full un-parametrised query.

If your query is stored server-side then perhaps create a stored procedure to accept @CompanyName as a parameter (you will need to write your DECLARE in your usp, as previously mentioned). Then, again using vba code, you can dynamically create the EXEC statement to execute against the connection.

These are just a couple of options, I'm sure there are others. But if you are looking for code as an answer, then I think you may be out of luck as there is more going on here than just "declare the scalar variable @CompanyName".

  • Many thanks for the responses Edgar and DimPerson. I did suspect that this might be a bit more involved, and the example query given is actually the simplest of several join queries. Since the database is old and no longer written to, static exports of data with searchable / filterable columns will actually do fine. – Sam Dec 15 '17 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.