How To Retrieve Information from SQL Server on Rows Affected

• Microsoft Visual FoxPro for Windows, version 5.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

      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 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.