DOCUMENT:Q141140  11-AUG-1999  [foxpro]
TITLE   :HOWTO: Create a SQL Server Stored Procedure
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:kbcode kbinterop kbvfp300 kbvfp500 kbvfp600

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

This article explains how to create a SQL Server stored procedure. The example
uses the Pubs database and the Authors table that ships with Microsoft SQL
Server. It is assumed that a valid data source has been created, so the article
doesn't discuss how to create a data source.

MORE INFORMATION
================

Creating a server-based stored procedure for frequently called queries will
dramatically improve performance. Once a stored procedures has been successfuly
created on the server, the server doesn't have to parse, check syntax, and
compile prior to executing. Parameters can be passed so that specific
information is retrieved. The following examples shows how to retrieve authors
from a specific state. To create a stored procedure on SQL Server, you must have
the authority to do so. Check with the DBA to ensure that you have proper
authority. The following example uses a data source called test and will create
a stored procedure to retrieve only those authors who live in California.

Code Sample
-----------

    hand = sqlconnect("test","sa","")

    ************************************
    * Check for good connection handle *
    ************************************
    IF hand > 0

      z = SQLEXEC(hand, "create procedure pick_state @mystate char(2) as " + ;
             "Select * from authors where state = @mystate")

       *******************************
       *Check for good SQL execution *
       *******************************
       IF z > 0
          WAIT WINDOW "Stored Procedure created"
       ELSE
          WAIT WINDOW "Stored Procedure failed"
          =SQLDISCONNECT(hand)
          CANCEL
       ENDIF

       ****************************
       * Execute Stored Procedure *
       ****************************
       =SQLEXEC(hand, "execute pick_state CA")
       * Display Result Set *
       BROWSE

       =SQLDISCONNECT(hand)

    ELSE
       WAIT WINDOW "Bad connection"
    ENDIF

Additional query words: speed up quicker faster

======================================================================
Keywords          : kbcode kbinterop kbvfp300 kbvfp500 kbvfp600 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP500 kbVFP600
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.