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.
» 
Comment and Contribute
