How to pass parameters to a SQL Server stored procedure
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) ASSELECT @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 + "'"=SQLEXEC(lnConn, lcCommand)BROWSEUSE*!* 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"=SQLEXEC(lnConn, lcCommand)BROWSEUSE*!* 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.=SQLEXEC(lnConn, lcCommand)?lcVersion =SQLDISCONNECT(lnConn) && clean up.
(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Garrett Fitzgerald, Microsoft Corporation.
Article ID: 247370 - Last Review: 01/24/2005 18:35:51 - Revision: 3.4
- kbclientserver kbdatabase kbhowto kbsqlprog KB247370