execute store proceduce in microsoft excel 2008

Question

execute store proceduce in microsoft excel 2008

girl_bj

SSCommitted

Points: 1731
More actions
November 2, 2014 at 7:29 am

#74503

How can I execute a store procedure with pass in parameter in excel 2008?
Example exec storeProcedure_A '2014-11-01'

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

As this is a forum for Microsoft Access, I think you will have better luck using an Excel forum. One that comes to mind is Windows Secrets, and the post ADP versus ODBC is an example. I should add that you will need to be well versed in using VBA in Excel, and also understand how to use either ADO or DAO to open a recordset from SQL Server as well as understanding how to construct a stored procedure to return data.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Try the demos in this article.

http://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Quick thought, normally would do this using VBA function wrapper, especially if the output spans multiple cells and/or the procedure has parameters. Very powerful stuff if done properly.

😎

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

You do not have to know how to write VBA for this task. I have done this in Excel for a business analyst before and it is quite simple.

I was trying to find the original article I used but did find this one that is pretty good walk-through:

Running a SQL Stored Procedure from Excel with Dynamic Parameters[/url]

I will point out that this article has a "step 11" that involves VBA code, I never did this in my situation and it worked fine for the user.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

girl_bj SSCommitted Points: 1731 More actions · Answer 5

girl_bj

SSCommitted

Points: 1731

November 2, 2014 at 6:47 pm

#1756245

Hi

Shawn Melton

The link not working..

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 6

girl_bj (11/2/2014)
Hi
Shawn Melton
The link not working..

I corrected it, tag for the URL was not formatted right.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

girl_bj SSCommitted Points: 1731 More actions · Answer 7

Manage to connect and result appeared.

How to pass in the parameter?

Don't understand on step 10.

Any step by step guidance?

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 8

How to pass in the parameter?

The parameter is passed in for this article on step 6.

Don't understand on step 10.

The author is creating an additional piece to be able to allow the user to select a parameter value from the drop down in Excel. This coincides with step 11. You are done at step 9, if the command text you entered in step 6 is correct where you procedure returns data.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

girl_bj SSCommitted Points: 1731 More actions · Answer 9

Yes I would like to pass in the parameter.

Where to store the VBA part? shown in step 10-11.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 10

girl_bj (11/2/2014)
Yes I would like to pass in the parameter.
Where to store the VBA part? shown in step 10-11.

I don't do VBA but expect there are walkthroughs on the web for that type of thing.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

gand 29575 SSChasing Mays Points: 617 More actions · Answer 11

To store the vba code you can record a macro. You can then assign the macro to a key or button. The Macro menu is on the View tab or the Developer tab (if you have it). There's lots of tutorials on the web on how to record an Excel macro.

Better still, click help in MSExcel and enter macro in the search box!

If you want to use wizards (no vba code), Microsoft Query still does the job well. You'll find it on the Data tab -> Get External Data From Other Sources -> From Microsoft Query. This creates an ODBC connection (you may have to define the data source using ODBC manager first) which allows you to use dynamic parameters in cells. Just make sure that you format the cell(s) for the date parameters as text (not date format). For MSSqlServer YYYY-MM-DD form works fine. Excel then passes it on to the db as varchar and it is automatically converted.

Use a fixed parameter initially to return results to MSExcel. Then edit the Command Text box to (example):

EXEC dbo.SalesAvgUCst ?,? or {Call dbo.SalesAvgUCst (?,?)}

Each ? wildcard is a placeholder for one parameter - which can be linked to cells via the parameters box which only becomes active after you enter the wildcards. Be sure to click on help in Microsoft Query and search for Stored Procedures for detailed step-by-step instructions.

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 12

"allows you to use dynamic parameters in cells"

What are dynamic parameters in cells?

gand 29575 SSChasing Mays Points: 617 More actions · Answer 13

For context please refer to link given by Shawn Melton.

Essentially you link a parameter to a spreadsheet cell reference which contains the value of the parameter. You can then change the value of the parameter (for example date) in the cell each time you run the procedure.