How Call Procedure 
Author Message
 How Call Procedure

Dear All,

Pls give me illustrate how i can create
store procedure in sqlserver with parameter and call it on foxpro

TIA
Rgds,
Sutrisno



Fri, 10 Dec 2004 17:11:36 GMT  
 How Call Procedure
straight from Microsoft knowledge base

SUMMARY
There are two ways to pass parameters to a stored procedure using SQLExec.
One way, which works across all versions of Visual FoxPro, is to build the
SQL command as a string variable. The advantage of this method is that you
can check the string and see exactly which SQL command you are passing to
the back end.

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 6.0, this
allows you to obtain values from the stored procedure that are being
returned as output parameters.

MORE INFORMATION

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

in a cursor. Here, we assign that result to an output parameter of the
stored procedure.





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)
BROWSE
USE

*!* 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)
BROWSE
USE

*!* To get a value back from a stored procedure, initialize the
*!* output variable first. This won't work under 3.0.
lcVersion = SPACE(200)

reference.
=SQLEXEC(lnConn, lcCommand)
?lcVersion

=SQLDISCONNECT(lnConn)  && clean up.


Quote:
> Dear All,

> Pls give me illustrate how i can create
> store procedure in sqlserver with parameter and call it on foxpro

> TIA
> Rgds,
> Sutrisno



Fri, 10 Dec 2004 21:30:35 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Calling Procedures and Functions - VFP 6.0--When Set Procedure Doesn't Set

2. Calling Procedures in a Report (VFP 3.0)

3. Calling Procedures and Functions - VFP 6.0

4. Calling Procedures and Functions - VFP 6.0

5. CALLING SYBASE STORE PROCEDURES FROM VFP

6. Inconsistent Procedure Calling

7. Calling Sybase Stored Procedures from VFP

8. calling oracle stored procedure

9. Stored Procedure call Via ODBC

10. How can i call a stored procedure with =SQLEXEC()

11. Call a procedure from submenu

12. calling internal procedure?

 

 
Powered by phpBB® Forum Software