The other way is to pass the Foxpro variables preceded with question marks, as in a parameterized view. In Visual FoxPro version 5.0 and later versions, this allows you to obtain values from the stored procedure that are being returned as output parameters.
- Create two stored procedures in SQL Server (see Books Online for the exact steps). Mysp_ObjectList merely takes the SysObjects table and returns the value you pass it once for every record in the table. In mysp_GetVersion, we elaborate a bit on the normal procedure for finding the version of the server. SELECT @@VERSION normally returns the SQL Server version as a record in a cursor. Here, we assign that result to an output parameter of the stored procedure.
CREATE PROCEDURE mysp_GetVersion @tcVersion Char(200) Output AS
SELECT @tcVersion = @@VERSION
CREATE PROCEDURE mysp_ObjectList @tcParm1 CHAR(10) AS
SELECT @tcParm1, name FROM sysobjects
- Create a DSN called SPParmTest in the ODBC Administrator which links to the database where you created the above procedures.
- Run the following code in Visual FoxPro:
*!* Error-checking is omitted for the purposes of this sample:
*!* you should always check the return values from SQL Passthrough calls.
lnConn = SQLCONNECT("SPParmTest")
lcParm1 = "ReturnThis"
lcParm2 = "Then This"
*!* This is the first way, involving building a string
*!* containing the parameters.
lcCommand = "exec mysp_ObjectList '" + lcParm1 + "'"
*!* This is the second way, passing the FoxPro variables directly to
*!* the SQL command. This will work in 3.0.
lcCommand = "exec mysp_ObjectList ?lcParm2"
*!* To get a value back from a stored procedure, initialize the
*!* output variable first. This won't work under 3.0.
lcVersion = SPACE(200)
lcCommand = "exec mysp_GetVersion ?@lcVersion" && Note the pass by reference.
=SQLDISCONNECT(lnConn) && clean up.
(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Garrett Fitzgerald, Microsoft Corporation.