Query:Variables

[Phil]

Hi All,

You will have to excuse me if this is not the right forum for this sort of
question but it seemed like a good place to start. I am trying to return
some data from Sql Server, I will try and give as much detail as possible. I
have written a very complex query in TSQL which pass's in a selection of
defined variables into a selection of select statement, What I would like to
do if at all possible is rather that passing the variables into the stored
procedure in TSQL and populating tables and then just creating a basic select
statement in Microsoft Query, is it at all posssible to get the Excel Sheet
to ask for the variables, so in theory and it is very much a theory is there
a way of programming such a complex statement into Microsoft Query. I hope
that makes some sort of sense and I can paste the code if anyone needs it.

Thanks in advance Philip

[K Dales]

You can use parameters in a MSQuery query, and you can link them to cells on
your spreadsheet. Whether this will work for you depends on the complexity
of your SQL, I suppose, and how you need to use those parameters as variables
in your query. You would create the parameters in MSQuery using the notation
[], as in Access, then when you are back in Excel a right-click on the query
result range will let you set the Parameters option to say where these
parameters come from.

But, another option that might have even more advantage: Set up a
simplified version of the query (with no parameters) in MSQuery, just to get
much of the setup work to be done for you without having to code it all.
Then, once you have the querytable, you can edit its properties. The
CommandText property is the SQL statement, so you should be able to change
the CommandText to take care of your entire SQL, no matter how complex, and
you can incorporate cell values and VBA variables/calculations however you
need to in setting up the query text. This approach would combine the ease
of using MSQuery for the initial setup, but give you pretty much complete
flexibility on the actual SQL you end up sending to your database.

"Phil" wrote:

> Hi All,
>
> You will have to excuse me if this is not the right forum for this sort of
> question but it seemed like a good place to start. I am trying to return
> some data from Sql Server, I will try and give as much detail as possible. I
> have written a very complex query in TSQL which pass's in a selection of
> defined variables into a selection of select statement, What I would like to
> do if at all possible is rather that passing the variables into the stored
> procedure in TSQL and populating tables and then just creating a basic select
> statement in Microsoft Query, is it at all posssible to get the Excel Sheet
> to ask for the variables, so in theory and it is very much a theory is there
> a way of programming such a complex statement into Microsoft Query. I hope
> that makes some sort of sense and I can paste the code if anyone needs it.
>
> Thanks in advance Philip