How to pass parameters to a SQL Server stored procedure

Article ID: 247370 - View products that this article applies to.
This article was previously published under Q247370
Expand all | Collapse all

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 later versions, this allows you to obtain values from the stored procedure that are being returned as output parameters.
Back to the top | Give Feedback

MORE INFORMATION

Follow these steps:
  1. 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
  2. Create a DSN called SPParmTest in the ODBC Administrator which links to the database where you created the above procedures.
  3. 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)
lcCommand = "exec mysp_GetVersion ?@lcVersion"  && Note the pass by reference.
=SQLEXEC(lnConn, lcCommand)
?lcVersion 

=SQLDISCONNECT(lnConn)  && clean up.
Back to the top | Give Feedback

REFERENCES

For additional information on calling Stored Procedures, click the article numbers below to view the articles in the Microsoft Knowledge Base:
156034
(http://support.microsoft.com/kb/156034/EN-US/ )
How To Retrieve Information from SQL Server on Rows Affected
177056
(http://support.microsoft.com/kb/177056/EN-US/ )
PRB: Return Parameter Variable Name for SQL Server Causes Error
114787
(http://support.microsoft.com/kb/114787/EN-US/ )
How To Execute a Stored Procedure on SQL Server

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Garrett Fitzgerald, Microsoft Corporation.

Back to the top | Give Feedback

Properties

Article ID: 247370 - Last Review: January 24, 2005 - Revision: 3.4
APPLIES TO
  • Microsoft Visual FoxPro 3.0b Standard Edition
  • Microsoft Visual FoxPro 5.0 Standard Edition
  • Microsoft Visual FoxPro 5.0a
  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Visual FoxPro 7.0 Professional Edition
  • Microsoft Visual FoxPro 8.0 Professional Edition
  • Microsoft Visual FoxPro 9.0 Professional Edition
Keywords: 
kbclientserver kbdatabase kbhowto kbsqlprog KB247370
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top