Google Answers Logo
View Question
Q: Parameterized SQL Stored Procedure Results in Exel using MS Query ( No Answer,   1 Comment )
Subject: Parameterized SQL Stored Procedure Results in Exel using MS Query
Category: Computers > Programming
Asked by: batman007-ga
List Price: $50.00
Posted: 30 Sep 2006 14:57 PDT
Expires: 30 Oct 2006 13:57 PST
Question ID: 769766
I'd like to call a stored procedure from Excel (via MS Query) but only
tables and views are visible in MS Query. The stored procedure itself
is on a SQL Server database and returns a table. It does not have
parameters. Is there a way to somehow wrap the output of the stored
procedure in a table or view so that it visible to MS Query?

Assuming the above is possible, is there a way to pass a parameter to
the stored procedure as well?

For both, I want to use the built in "New Database Query" UI and not
have to resort to programming.
There is no answer at this time.

Subject: Re: Parameterized SQL Stored Procedure Results in Exel using MS Query
From: eugenez2-ga on 21 Oct 2006 08:15 PDT
<I'd like to call a stored procedure from Excel (via MS Query)>
--one of ways--> [case when DSN for sql server was created and tested]
MS Query-> 
            Execute SQL->select there your "DataSource"->
          in "SQL statment" type (for example:) exec master.dbo.sp_who ->Execute
You will get result 
If you'd like to use the stored procedure parameter- no problems:
from menu: View->SQL (or just click "SQL" bt)
type there (e.g)
exec master.dbo.sp_who 53
Cleck "OK"
you will get the result

<<<Execute an SQL statement or procedure>>>>>
If you know Structured Query Language (SQL), you can use SQL to
execute an SQL SELECT statement. For more information about SQL, see
the documentation for your database.

On the File menu, click Execute SQL. 
To execute an SQL statement on a table in another data source, click
Data Sources, click the data source you want, and then click OK. You
can specify a new data source.


In the SQL statement box, type the SQL statement you want to run. 
If you have an existing SQL statement you want to run, click Open,
click the SQL file, and then click Open.

To run an SQL procedure stored in your external database, click
Procedures, click the procedure you want in the Query box, and then
click Paste to copy the procedure to the SQL statement box.

Click Execute. 
Microsoft Query runs the SQL statement. If the query is taking a long
time to return data, you can cancel the query by pressing ESC.

To save the SQL statement, click Save.


To change or replace the SQL statement, use the same SQL syntax that
Microsoft Query uses or the syntax your data source supports.
Microsoft Query tries to interpret the syntax you use and displays
both the query's design and its result set.
If Microsoft Query can't display the query's design because it can't
interpret a portion of the syntax you used, the result set is still
displayed. However, you can change only the result set's formatting,
such as the display font or column width. You can't edit data, and you
must make any change to the query definition, such as adding or
removing fields, in the SQL dialog box.
If the query returns more than one result set, Microsoft Query
displays the first one in the Data pane. Any other result sets are not
If the query runs successfully but does not return a result set,
Microsoft Query displays a message that the statement was run
successfully. If the query affected rows in the underlying tables, the
message also specifies how many rows were affected.
If the query didn't run successfully, Microsoft Query displays the SQL
error message that corresponds to the problem. To display the Execute
SQL dialog box and correct the statement, click OK.


<<<Microsoft Query won't let me edit parameter types for my SQL procedure.>>>>

To create parameters in a SQL stored procedure and set the data types:

On the File menu, click Execute SQL. 
In the SQL statement box, create your query using fixed arguments for
the procedure, and then click Save.
On the View menu, click SQL. 
In the SQL statement box, replace each fixed argument with a question mark (?). 
Click OK, and then click Parameters on the View menu. 
To change the data type for a parameter, click the parameter and then click Edit.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy