Practice: Create a New Stored Procedure to Meet a Business Need
As an illustrative example, let's say that we are next asked by the accounting department of our publishing organization for information to meet a recurring business need. Accounting needs a crosstab report that summarizes, from the Pubs database, quarterly sales volumes by sales year.
The information consumers from whom we gather the business requirements state that the report will become a recurring requirement, and will be run frequently to update quarterly volumes that, in our business, are subject to adjustments due to returns and the like. Our developers have the same restrictions that we mentioned in our first example with regard to the MS Access connection being their only connection to the database.
The query required to fill the requirement will be a likely candidate for a Stored Procedure, at least as one means of helping the information consumers to meet their stated objectives. Once we create such a Stored Procedure, we can generate the report prospectively at any time, with the click of a mouse.
Let's meet the needs of the information consumers by taking the following steps:
1. From the Query window, where we left MS Access in our last exercise, click New atop the window:
The New Query dialog appears.
2. Click-select Design View, once again.
3. Click OK.
4. Click Close to dismiss the empty Show Table dialog box that appears.
We will use the Pass-Through query option as a conduit, once again, through which to send a CREATE PROCEDURE statement that includes the query we need to generate the report for the information consumers. Through this channel, we will again communicate syntax that the targeted database server understands.
5. Select Query --> SQL Specific --> Pass-Through on the Query menu, as we did in the earlier section.
The SQL Specific editor appears.
6. Click the Properties icon atop the view, again as we did earlier.
The Query Properties page opens. This time, we will assign connection information for the back-end server using the data source we created in the first part of our exercises, Pubs_DB.
7. Click Build, the ellipses icon ("...") that appears on the right side of the ODBC Connect Str box on the Query Properties page, as we did earlier.
The Select Data Source dialog appears, defaulted to the File Data Source tab.
8. Click the Machine Data Source tab.
9. Click-select the Pubs_DB data source we created in the first section, as shown in Illustration 27.
Illustration 27: Select the Pubs_DB Data Source
10. Click OK.
11. On the Connection String Builder message box that next appears, click the answer you gave earlier, in our initial response to its question regarding the storage of the password.
As before, I clicked Yes here, as I am in a development environment with minimal risk involved in storing a password in the connection string. Please see the warning in the first section of this article regarding doing so in your own environment.
We are returned to the Query Properties page, where we see the new string specification appear in the ODBC Connect Str box, as we noted in the first section of the article.
Another property we need to consider, again because it is of significance within our intended use of the Pass-Through query channel, is the Returns Records setting. This time, we will set it to No, as shown in Illustration 28.
Illustration 28: The Properties Page, with Our New Settings
We will be writing a one-time CREATE PROCEDURE statement, containing the query necessary to generate the report we have been tasked to create, to place the custom Stored Procedure on the back-end SQL Server. Then, anytime the data needs to be generated, we can execute the Stored Procedure to obtain updated information for our information consumers. No recordset will be returned from the CREATE PROCEDURE statement, therefore Return Records is appropriately set to No.
12. Close the Query Properties page.
We are returned to the SQL Pass-Through Query window.
We are now ready to create a back-end Stored Procedure.