How to execute parametrised stored procedure from MS query?

[Guest]

Hi !
How to execute a parametrised stored procedure from MS query to MS SQL
database? If it's possible.

Regards
Otto

 

[NickHK]

Otto,
Using ADO ?

Check the Command and .CommandType, .Parameters.Add etc.
I seem to remember that even though there is a
comm.CommandType = adCmdStoredProc
you need to use
CommandType = adCmdText
for this to work.

NickHK

 

[Greg Glynn]

Hi Otto,

You can certainly do it.

Set up your MSQUERY and add your parameter fields eg: In MSQUERY,
select a field in the criteria field and then in the Value, use
something like

=[%MYDATE]

So MYDATE becomes a parameter field. When you save the query in the
editor, you'll be prompted for the value of %MYDATE - leave it blank.

Back in Excel, put the query results somewhere, then Right-Click on the
Top Row (where the field names are). Select EDIT PARAMETERS. You'll
be offered the list of your parameter fields and some options on how to
deal with them.

Option1 - Prompt for the Value
Option2 - Set a default value
Option3 - Get the value from a CELL

Remember that where using DATES, MSQUERY wants dates in this format
30-SEP-06, which can be a pain.

Regards


Greg