<LI><I>Blue</I> for keywords such as PRINT and SELECT
	<P>
	<LI><I>Green</I> for both styles of comment
	<P>
	<LI><I>Black</I> for other kinds of text
</UL>

<P>To run a stored procedure, choose Tools, Run; or right-click the stored procedure
name in DataView and choose Run; or right-click in the editor and choose Run. The
results appear in the Results pane of the Output window--don't confuse this with
the Results pane of Query Designer. Figure 23.22 shows the Output window stretched
very large to show some results of reptq2.</P>
<P><A HREF="javascript:popUp('23uvc22.gif')"><B>FIG. 23.22</B></A><B> </B><I>You
can see the results of any stored procedure from within Developer Studio.</I></P>
<P>Some stored procedures take parameters. For example, double-click reptq3; its
code looks like this:</P>
<P>
<PRE>CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select pub_id, type, title_id, price
from titles
where price &gt;@lolimit AND price &lt;@hilimit AND type = @type 
      OR type LIKE `%cook%'
order by pub_id, type
COMPUTE count(title_id) BY pub_id, type
</PRE>
<P>This stored procedure takes three parameters: lolimit, hilimit, and type. If you
run it, the dialog box shown in Figure 23.23 appears: Enter parameter values and
click OK to run the procedure. See the results in the Output window.</P>
<P><A HREF="javascript:popUp('23uvc23.gif')"><B>FIG. 23.23</B></A><B> </B><I>Providing
parameters to stored procedures is simple.</I></P>
<P>It might be nice if the type parameter were a drop-down box, enabling you to see
all the type values in the table before submitting the query rather than having to
type business yourself. That sort of capability is exactly what you can build into
a C++ program that uses SQL stored procedures. To see how, in the next section you
will write a new stored procedure and call it from your C++ program.</P>
<P>
<H3><A NAME="Heading10"></A>Writing a New Stored Procedure</H3>
<P>To create a new stored procedure, right-click Stored Procedures in DataView and
choose New Stored Procedure. This code appears in the editor:</P>
<P>
<PRE>Create Procedure /*Procedure_Name*/
As
     return (0)
</PRE>
<P>Edit this code so that it looks like Listing 23.1. Save the stored procedure by
choosing File, Save--there's no need to specify the name because it's in the first
line. After the procedure has been saved, its name appears in the DataView.</P>
<P>
<H4>Listing 23.1&#160;&#160;author_ytd, the New Stored Procedure</H4>
<PRE>CREATE PROCEDURE author_ytd @sales int
AS
SELECT authors.au_lname, authors.au_fname, titles.title, ytd_sales
   FROM authors, titles, titleauthor
   WHERE ytd_sales &gt; @sales 
      AND authors.au_id = titleauthor.au_id 
      AND titleauthor.title_id = titles.title_id
</PRE>
<PRE>ORDER BY ytd_sales DESC
</PRE>
<P>This SQL code gathers information from three tables, using the au_id and title_id
columns to connect authors to titles. It takes one parameter, sales, which is an
integer value. Run the procedure to see the results immediately. Listing 23.2 shows
the results, using 4000 as the value for sales.</P>
<P>
<H4>Listing 23.2&#160;&#160;author_ytd results (@sales = 4000)</H4>
<PRE>Running Stored Procedure dbo.author_ytd ( @sales = 4000 ).
au_lname        au_fname  title                                     ytd_sales
-------------- -------- -------------------------------------------- ------
DeFrance        Michel    The Gourmet Microwave                        22246
Ringer          Anne      The Gourmet Microwave                        22246
Green           Marjorie  You Can Combat Computer Stress!              18722
Blotchet-Halls  Reginald  Fifty Years in Buckingham Palace Kitchens    15096
Carson          Cheryl    But Is It User Friendly?                      8780
Green           Marjorie  The Busy Executive's Database Guide           4095
Bennet          Abraham   The Busy Executive's Database Guide           4095
Straight        Dean      Straight Talk About Computers                 4095
Dull            Ann       Secrets of Silicon Valley                     4095
Hunter          Sheryl    Secrets of Silicon Valley                     4095
O'Leary         Michael   Sushi, Anyone?                                4095
Gringlesby      Burt      Sushi, Anyone?                                4095
Yokomoto        Akiko     Sushi, Anyone?                                4095
White           Johnson   Prolonged Data Deprivation: Four Case Studies 4072
 (14 row(s) affected)
Finished running dbo.author_ytd. 
</PRE>
<PRE>RETURN_VALUE = 0
</PRE>
<H3><A NAME="Heading11"></A>Connecting the Stored Procedure to C++ Code</H3>
<P>At the moment, you have an empty C++ application that uses a recordset and would
display members of that recordset in a record view if you added fields to the dialog
to do so. The recordset contains all the columns from the three tables (authors,
titleauthor, and titles) that you specified during the AppWizard process. That's
arranged by a function called CPublishingSet::GetDefaultSQL() that AppWizard wrote
for you, shown in Listing 23.3.</P>
<P>
<H4>Listing 23.3&#160;&#160;CPublishingSet::GetDefaultSQL() from AppWizard</H4>
<PRE>CString CPublishingSet::GetDefaultSQL()
{
     return _T(&quot;[dbo].[authors],[dbo].[titleauthor],[dbo].[titles]&quot;);
</PRE>
<PRE>}
</PRE>
<P>You're going to change this default SQL so that it calls your stored procedure,
which is now part of the pubs database. First, choose Project, Set Active Project
and select Publishing. Switch to ClassView in the Workspace pane, expand CPublishingSet,
and double-click GetDefaultSQL() to edit it. Replace the code with that in Listing
23.4.</P>
<P>
<H4>Listing 23.4&#160;&#160;CPublishingSet::GetDefaultSQL() to Call Your Stored Procedure</H4>
<PRE>CString CPublishingSet::GetDefaultSQL()
{
     return _T(&quot;{CALL author_ytd(4000)}&quot;);
</PRE>
<PRE>}
</PRE>


<BLOCKQUOTE>
	<P>
<HR>
<strong>NOTE:</strong>ormally you would not hard-code the parameter value like this. Adding
	member variables to the class to hold parameters and passing them to the SQL is a
	topic you can explore in the online help when you are more familiar with the Enterprise
	Edition.&#160; 
<HR>


</BLOCKQUOTE>

<P>The records returned from this query will go into your recordset. The query returns
four columns (au_lname, au_fname, title, and ytd_sales), but the recordset is expecting
far more than that. You can use ClassWizard to edit your recordset definition. Follow
these steps:</P>

<DL>
	<DT></DT>
	<DD><B>1. </B>Open ClassWizard by choosing View, ClassWizard.
	<P>
	<DT></DT>
	<DD><B>2. </B>Click the Member Variables tab. You should see something like Figure
	23.24, showing all the member variables of the recordset connected to table columns.
	<P>
</DL>

<P><A HREF="javascript:popUp('23uvc24.gif')"><B>FIG. 23.24</B></A><B> </B><I>ClassWizard
manages your recordset definition.</I></P>

<DL>
	<DT><I></I></DT>
	<DD><B>3. </B>Highlight [address] and click Delete Variable.
	<P>
	<DT></DT>
	<DD><B>4. </B>In the same way, delete all the variables except au_lname, au_fname,
	title, and ytd_sales.
	<P>
	<DT></DT>
	<DD><B>5. </B>Click OK to close ClassWizard.
	<P>
</DL>

<P>Your application can compile and run now, but until you edit the Record View dialog
box, you won't be able to see the records and columns that are returned by another
query. Editing the dialog box is covered in Chapter 22 and uses skills first demonstrated
in Chapter 2, &quot;Dialogs and Controls,&quot; so the description here will be brief.</P>
<P>Click the ResourceView tab, expand the resources, expand Dialogs, and double-click
IDD_PUBLISHING_FORM. This dialog box was created for you by AppWizard but has no
controls on it yet. Delete the static text reminding you to add controls, and add
four edit boxes and their labels so that the dialog resembles Figure 23.25. Use sensible
resource IDs for the edit boxes, not the defaults provided by Developer Studio. Name
them IDC_QUERY_LNAME, IDC_QUERY_FNAME, IDC_QUERY_TITLE, and IDC_QUERY_YTDSALES.</P>
<P><A HREF="javascript:popUp('23uvc25.gif')"><B>FIG.23.25</B></A><B> </B><I>Edit
your Record View dialog box.</I></P>
<P>There is one task left: Connect these fields to member variables. Here's how to
make that connection:</P>

<DL>
	<DT></DT>
	<DD><B>1. </B>Open ClassWizard while this dialog box has focus.
	<P>
	<DT></DT>
	<DD><B>2. </B>Click the Member Variables tab.
	<P>
	<DT></DT>
	<DD><B>3. </B>Select IDC_QUERY_FNAME and click Add Variable to open the Add Member
	Variable dialog box.
	<P>
	<DT></DT>
	<DD><B>4. </B>From the drop-down box labeled Member Variable Name, choose m_pSet-&gt;m_au_fname
	and click OK.
	<P>
	<DT></DT>
	<DD><B>5. </B>In the same way, connect IDC_QUERY_LNAME to m_pSet-&gt;m_au_lname,
	IDC_QUERY_TITLE to m_pSet-&gt;m_title, and IDC_QUERY_YTDSALES to m_pSet-&gt;m_ytd_sales.
	<P>
	<DT></DT>
	<DD><B>6. </B>Figure 23.26 shows the ClassWizard dialog box when all four controls
	have been connected. Click OK to close ClassWizard.
	<P>
</DL>

<P>In ClassView, double-click the function DoFieldExchange() under CPublishingSet
and look at the code that was generated for you. The order in which the variables
appear in this code is important: It must match the order in which the fields are
coming back from your stored procedure. Figure 23.27 shows DoFieldExchange() and
the stored procedure together. Adjust the order of the fields in the SELECT statement,
if required.</P>
<P>Build your project and run it. You should see a record view like Figure 23.28
(you might have to go through the SQL login procedure again first), and if you scroll
through the record view with the arrow buttons, you should see every author from
the report in Listing 23.2.</P>
<P><A HREF="javascript:popUp('23uvc26.gif')"><B>FIG. 23.26</B></A><B> </B><I>Connect
the record view controls to member variables of the recordset.</I></P>
<P><A HREF="javascript:popUp('23uvc27.gif')"><B>FIG. 23.27</B></A><B> </B><I>Make
sure that the fields are in the same order in DoFieldExchange() as in your stored
procedure.</I></P>
<P><A HREF="javascript:popUp('23uvc28.gif')"><B>FIG. 23.28</B></A><B> </B><I>Your
application displays the results of the stored procedure's query.</I></P>


<BLOCKQUOTE>
	<P>
<HR>
<strong>TIP:</strong> Make sure you have saved the SQL stored procedure before you build.
	Because the stored procedures are in a subproject of Publishing, building Publishing
	will not trigger any saves in the subproject. 
<HR>


</BLOCKQUOTE>

<P>This application doesn't do much at the moment: It calls a stored procedure and
neatly presents the results. With a little imagination, you can probably see how
your SQL-based C++ programs can wrap stored procedures in user-friendly interfaces