Table in Excel from SQL Server stored procedure with parameter field in workbook
I got it to work using the steps provided at the following linked blog post.
http://codebyjoshua.blogspot.com/2012/01/get-data-from-sql-server-stored.html
Steps copied here in case link breaks in the future.
Excel 2007 Instructions:
Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"
Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.
Close the "Add Tables" popup if necessary.
Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.
Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}
For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}
Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.
Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.
You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.
Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.
You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.
Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.
Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.
If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"
Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.
You should now have some data straight from your stored procedure.
Table in Excel 2010 from SQL Server stored procedure with parameter field in workbook
aaaha ! I got it, I guess at first I did not research well.
See on this link :
https://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/
Steps below from the above provided link.
Step 1: Data tab – > From Other Sources -> From SQL Server
Step 2: Enter Credentials. Your server name can be an IP address
Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.
Step 4: Excel will pop up the Import Data dialog box. Click Properties here (NOT THE OK BUTTON).
Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.
Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).
Step 7: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.
Step 8: Marvel at your results
stored proc from excel with parameters
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.
- Under the 'Connections' section click 'Connections'
- Click Add
- Click 'Browse for More...'
- Click 'New Source...'
- Select 'Microsoft SQL Server'
- Click Next
- Type in the login credentials and click ok.
- Select your database
- Uncheck 'Connect to a specific table'
- Click Finish.
- When asked to select a table just click ok.
- This should bring you back to the Workbook Connections screen with your new connection added.
- Select it and click 'Properties'
- Go to the 'Definition' tab.
- Set the Command Type to SQL
- set the Command text to the SQL you want to run (i.e. "exec spStoredProc 'value'")
- Set a new name for your connection
- Click ok.
- if told that you are severing a connection just agree that, that is okay.
- Click 'Close' on your 'Workbook Connections'
- Click 'Existing Connections' under the 'Get External Data' section
- Select your connection you just made under the 'Connections in this Workbook' section.
- Click Open
- Set your Import Data options if you want or just click ok
- You should now see the result set from the SQL Query in your excel worksheet.
- This data can be refreshed from the data that is in the database by going to the data tab and clicking 'Refresh All'
Related Topics
Sql-Server: Error - Exclusive Access Could Not Be Obtained Because the Database Is in Use
Singular or Plural Database Table Names
How to Group by Week in Postgresql
How to Get Previous Business Day in a Week with That of Current Business Day Using SQL Server
How to Speed Up Row_Number in Oracle
MySQL Query to Select Everything Except
Postgresql: Between with Datetime
Oracle "Ora-01008: Not All Variables Bound" Error W/ Parameters
SQL Count Total Number of Rows Whilst Using Limit
Identity_Insert Is Set to Off - How to Turn It On
How to Combine 2 Select Statements into One
Difference Between a Statement and a Query in SQL
What Free SQL Formatting Tools Exist
Oracle: How to Get Percent of Total by a Query
Fifo Implementation in Inventory Using SQL