Parameters from MS Excel into SQL Query

[StevenK]

I'm trying to provide clients with some means of running an SQL statement against an SQL Server database.
I'm able to do this with static text query strings - and the user is then able to refresh these.

I set the query up via Microsoft Query (through 'Data' - 'Import External Data' - 'New Database Query' option).

However when I want to do more complex queries - I first set up the SQL and test this through SQL Server Query Analyser.
When this is copied and pasted into the Microsoft Query editor (from use via Excel) I encounter problems:

- Sometimes I'm told 'SQL Query can't be represented graphically. Continue anyway?' (if there's LEFT JOIN statements included for example).
- I find myself unable to make use of parameters (i.e. declaring and setting these in the SQL editor) - if I do this then when the result set (albeit shown in Microsoft Query editor) is returned to MS Excel - it simply reads 'Query from Connection_ODBC' (where 'Connection_ODBC' is the ODBC connection I'm making use of to access the SQL Server database).

 

[StevenK]

(Sorry, hit the 'Post' button too early!)

I'd like to be able to write more complex queries such that the users can make use of these through MS Excel.

Once I'm able to do this - is it then possible to pass parameters into the SQL statement to be run (i.e. from specific cells in the MS Excel spreadsheet) ?

Any help on any of these points would be appreciated.

Thanks in advance,
Steve