HOWTO: Executing a Stored Procedure on SQL Server

• Microsoft Visual FoxPro for Windows, version 3.0, 5.0
• Microsoft FoxPro Connectivity Kit, version 2.5
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6

SUMMARY

By using the FoxPro Connectivity Kit's DBExec() function, you can execute a stored SQL Server procedure.

MORE INFORMATION

The steps below 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 DBExec() function from FoxPro.

Below are the steps to set up a stored procedure on SQL Server and how to execute it from FoxPro using the Connectivity Kit.

Setting Up a Stored Procedure on SQL Server

1. From the Microsoft SQL Administrator, choose the Query button.

2. Type the following in the Query window:

         use pubs
   

3. Choose the Execute button.

4. Delete the information in the Query window.

5. Type in the following procedure:

         create procedure showsales @parm1 char(4)
         as
         select * from sales where stor_id=@parm1
   

6. From the File menu, choose Save As and save the procedure as STOR_PRC.SQL.

7. Choose the Execute button.

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

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