Calling Views and Stored Procedures from Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 217

5. Click OK in the Connection Properties dialog box to confirm your change, and then click the
Close button on the Workbook Connections dialog box.

Excel automatically connects to the data source and runs your newly added SQL statement. If all goes
well, you see your updated data. If Excel encounters an error, a message box opens describing what
went wrong.

It's generally a good idea to test your SQL statement before entering it into Excel. This
simply means running the SQL statement on the database server to ensure you get the
expected results. See Chapter 9 for a refresher on how to create and run SQL statements
on the database server.

Note

Running stored procedures from Excel
In Chapter 10, you discovered the benefits of creating stored procedures in SQL Server. The ability to
manually enter your own SQL statements into a connection enables you to call a stored procedure
right from Excel! For example, the SQL statement in Figure 11-6 executes the SP_MarketSummary
stored procedure.

Figure 11-6: Running an SQL Server stored procedure from Excel.

Some stored procedures require parameters (criteria inputs) to run successfully. If your stored proce-
dures require parameters to run, you can simply include them in your SQL statement. Figure 11-7
shows a stored procedure with two parameters: one that passes the required market name, and one
that passes the required quarter.