stored proc from excel with parameters

Question

If I create a stored proc in sql that has a parameter, can I run that from Excel?

Answer 1

Sure, you can use VBA.

Or you can do it like this: http://blogs.msdn.com/b/excel/archive/2010/06/08/running-a-sql-stored-procedure-from-excel-no-vba.aspx

Here's another nice article on it.

http://www.itjungle.com/mgo/mgo102203-story01.html

Answer 2

Yes

A standard ADO call from VBA. Or use the Tools..Data thing.

Answer 3

Without using VBA you can use this method. This assumes your stored procedure returns a result set. It also does not make the parameter pulled from a workbook. I think you'd have to use VBA to do that.

1. Under the 'Connections' section click 'Connections'
2. Click Add
3. Click 'Browse for More...'
4. Click 'New Source...'
5. Select 'Microsoft SQL Server'
6. Click Next
7. Type in the login credentials and click ok.
8. Select your database
9. Uncheck 'Connect to a specific table'
10. Click Finish.
11. When asked to select a table just click ok.
12. This should bring you back to the Workbook Connections screen with your new connection added.
13. Select it and click 'Properties'
14. Go to the 'Definition' tab.
15. Set the Command Type to SQL
16. set the Command text to the SQL you want to run (i.e. "exec spStoredProc 'value'")
17. Set a new name for your connection
18. Click ok.
19. if told that you are severing a connection just agree that, that is okay.
20. Click 'Close' on your 'Workbook Connections'
21. Click 'Existing Connections' under the 'Get External Data' section
22. Select your connection you just made under the 'Connections in this Workbook' section.
23. Click Open
24. Set your Import Data options if you want or just click ok
25. You should now see the result set from the SQL Query in your excel worksheet.
26. This data can be refreshed from the data that is in the database by going to the data tab and clicking 'Refresh All'