DOCUMENT:Q156034  21-AUG-1999  [foxpro]
TITLE   :HOWTO: Retrieve Information from SQL Server on Rows Affected
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:kbinterop kbAutomation kbvfp500 kbvfp600

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

Microsoft SQL Server and other Server data sources often have statistical
information regarding how many rows were affected by a particular SQL statement
when executed. Visual FoxPro does not retrieve this information from the server
for you. This article discusses how you can integrate the use of Stored
Procedures on the server to retrieve this information.

MORE INFORMATION
================

When you are working with data on a server, you need to know how many rows in
the table were affected by a certain SQL command, similar to the information
provided by the _TALLY variable in Visual FoxPro.

This information is not available to you directly within Visual FoxPro, but there
are ways to obtain that information, if necessary.

Visual FoxPro 5.0 adds support for output parameters from stored procedures. This
means that you can call a procedure located on the SQL Server and that procedure
will then return information to the client application.

The following example shows two pieces of code. The first is a SQL Server stored
procedure that takes two parameters, an order id and order amount. It then
returns the number of rows affected. The second program is FoxPro code showing
how to call the procedure and retrieve the return value from the server code:

      ** SQL Server Code
      CREATE PROCEDURE upd_morders @cust_id int, @order_amt int, @retcount int
   OUTPUT AS
         UPDATE morders SET order_amt = @order_amt
            WHERE cust_id = @cust_id
      SELECT @retcount = @@ROWCOUNT
      GO

      ** FoxPro Code
      mvar = 0
      retVal = SQLExec(1,'{CALL upd_morders (1, 22.50, ?@mvar)}')
      ? mvar

The SQL Server procedure takes two parameters, the customer id and the order
amount. It then updates a table on the server and returns the value @@ROWCOUNT,
which is a system-defined variable in Microsoft SQL Server containing the number
of records modified by the last SQL Statement.

The FoxPro code shows how to pass a FoxPro variable to the stored procedure and
have it filled in by the stored procedure.

REFERENCES
==========

More information can be found on SQL Server stored procedures in the Microsoft
SQL Server manuals. Information about Output parameters in Visual FoxPro can be
found in the Visual FoxPro documentation.

Additional query words:

======================================================================
Keywords          : kbinterop kbAutomation kbvfp500 kbvfp600 
Technology        : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP600
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.