Use parameterised SQL Server stored procedure in Excel database query

[Originally Posted By]: https://social.msdn.microsoft.com/Forums/en-US/d86fbdfa-82e2-4da7-92df-ae5f0f60090b/use-parameterised-sql-server-stored-procedure-in-excel-database-query?forum=isvvba

I managed to solve my problem.  Hopefully it will help you solve yours.

When you create a parameterized query in excel using a select statement like “Select * from mytable where xdate between ? and ?” two parameters are created:
“Parameter1”
“Parameter2”

When you change the command from a select statement to a stored procedure it should, but doesn’t, look for the same parameters.  After much trial and error I discovered excel is looking for:
“Parameter 1”
“Parameter 2”

Note the spaces.  While “Parameter1” is what was created with the excel query when using a select statement, “Parameter 1” is what excel is looking for when the command is referencing a stored procedure.

I found that creating the parameterized query as a stored procedure in the first place, instead of creating a select statement first and changing it later, seems to work just fine as long as the query is using the correct syntax.

That syntax is: “{CALL MyStoredProcedure (?,?)}”

So the steps on a new 2007 workbook:

1. go to Data tab, get external data from other sources, from Microsoft Query.
2. Select/Create DSN
3. Select a table and some columns to enable “next”
4. Click “next” until you can select “view data or edit query in Microsot Query”
5. Click Finish to open MS Query.
6. Click SQL button in toolbar
7. change SQL statement to “{CALL MyStoredProcedure (?,?)}” and click OK.
8. enter parameters.
9. close MS Query and tell Excel where you want the results.

You now have a parameterized SP as a Data connection in your workbook.  The parameters will have the spaces and will work properly.

There is probably a better way than this convoluted method, but at least it works.

  • Proposed as answer by wffOrdeith Saturday, September 26, 2009 5:23 PM
  • Marked as answer by danishaniModerator Saturday, October 04, 2014 3:59 PM

Saturday, September 26, 2009 12:26 AM

wffOrdeith

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s