Microsoft KB Archive/114787

= How To Execute a Stored Procedure on SQL Server =

Article ID: 114787

Article Last Modified on 2/22/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.6 Standard Edition
 * Microsoft FoxPro 2.5b for MS-DOS
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b for MS-DOS
 * Microsoft FoxPro 2.6 for MS-DOS

-



This article was previously published under Q114787



SUMMARY
By using Visual FoxPro's SQLExec function, or the FoxPro 2.x Connectivity Kit's DBExec function, you can execute a stored SQL Server procedure.



MORE INFORMATION
The following steps describe how to create a stored procedure on Microsoft SQL Server; this information has been presented as a matter of convenience and is not supported by FoxPro Product Support. For questions concerning the creation, syntax, or functionality of stored procedures, refer to the CREATE PROCEDURE command in the SQL Server "Language Reference," or contact SQL Server Product Support. The commands to create the stored procedure can also be executed with the SQLExec function from Visual FoxPro, or the DBExec function from FoxPro 2.x. The steps set up a stored procedure on SQL Server and then explain how you execute the stored procedure from within Visual FoxPro or from FoxPro 2.x using the Connectivity Kit.

Setting Up a Stored Procedure on SQL Server 7.0
 From the Programs menu select Microsoft SQL Server 7.0, and then choose Query Analyzer. Connect to your server. Choose the pubs database in the DB drop down.  Type in the following procedure: create procedure showsales @parm1 char(4) as select * from sales where stor_id=@parm1  From the File menu, choose Save As and save the procedure as STOR_PRC.SQL.  Click the Execute button.</ul> </li></ol>

Setting Up FoxPro to Run a Stored Procedure
Run one of the following programs, depending on your version of FoxPro.

FoxPro 2.x Code:
*****SET THE LIBRARY AND INITIALIZE VARS IF _DOS SET LIBRARY TO SYS(2004)+"fpsql.plb" ELSE SET LIBRARY TO SYS(2004)+"fpsql.fll" ENDIF PUBLIC errval PUBLIC errmsg PUBLIC handle errval=0 errmsg=' ' sourcename= 'test' user= 'sa' passwd=''

********CONNECT

handle=DBConnect(sourcename,user,passwd) IF handle > 0 WAIT WINDOW 'Successfully Connected' NOWAIT ELSE error=DBError(0,@errmsg,@errval) WAIT WINDOW STR(error)+' '+STR(errval)+' '+errmsg ENDIF

=DBSetOpt(handle,'Asynchronous',0) =DBSetOpt(handle,'BatchMode',1) =DBSetOpt(handle,'ConnTimeout',0) =DBSetOpt(handle,'Transact',1) =DBSetOpt(handle,'UseTable',0)

err=DBExec(handle,'use pubs') DO errhand WITH err,'USE PUBS'

**********THIS PROGRAM DEMOs HOW TO IMPLEMENT SQL WITH **********THE DBExec FUNCTION

sqlcomm= "execute showsales '7066'" err=DBExec(handle,sqlcomm) DO errhand WITH err,"DBExec(handle,"+sqlcomm+")" IF err > 0 BROWSE ENDIF

**********DISCONNECT err=DBDisconn(handle) DO errhand WITH err,"DBDisconn" SET LIBRARY TO  CLOSE ALL

**********Error Handler Program PROCEDURE errhand PARAMETERS err,command IF err > 0 WAIT WINDOW ALLTRIM(UPPER(command))+"Completed Successfully"; NOWAIT ELSE WAIT WINDOW UPPER(command)+"NOT Completed Successfully" error=DBError(handle,@errmsg,@errval) WAIT WINDOW STR(error)+" "+STR(errval)+" "+errmsg ENDIF RETURN Note that the program returns the two records that have 7066 as the stor_id.

Visual FoxPro Code:
PUBLIC errval PUBLIC errmsg PUBLIC handle errval=0 errmsg=' ' sourcename= 'test' user= 'sa' passwd=''

********CONNECT

* Turning on error display for connections =SQLSetProp(0,"DispWarning",.t.)

handle=SQLConnect(sourcename,user,passwd) IF handle > 0 WAIT WINDOW 'Successfully Connected' NOWAIT ENDIF

********Set some defaults =SQLSetProp(handle,'Asynchronous',.f.) =SQLSetProp(handle,'BatchMode',.t.) =SQLSetProp(handle,'ConnectTimeOut',0) =SQLSetProp(handle,'Transactions',1)

err=SQLExec(handle,'use pubs') DO errhand WITH err,'USE PUBS'

**********THIS PROGRAM DEMOs HOW TO IMPLEMENT SQL WITH **********THE SQLExec FUNCTION

sqlcomm= "execute showsales '7066'" err=SQLExec(handle,sqlcomm) DO errhand WITH err,"SQLExec(handle,"+sqlcomm+")" IF err > 0 BROWSE ENDIF

**********DISCONNECT err=SQLDisconnect(handle) DO errhand WITH err,"SQLDisconnect" CLOSE ALL

**********Error Handler Program PROCEDURE errhand PARAMETERS err,command IF err > 0 WAIT WINDOW ALLTRIM(UPPER(command))+"Completed Successfully"; NOWAIT ELSE WAIT WINDOW UPPER(command)+"NOT Completed Successfully" ENDIF RETURN Note that the program returns the two records that have 7066 as the stor_id.

Additional query words: CK STORED PROCEDURE ODBC

Keywords: kbhowto kbinterop kbcode KB114787

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.