DetailPage-MSS-KB

Knowledge Base

Article ID: 141140 - Last Review: February 28, 2014 - Revision: 3.4

This article was previously published under Q141140
This article has been archived. It is offered "as is" and will no longer be updated.

On This Page

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
				

APPLIES TO
  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft Visual FoxPro 5.0 Standard Edition
  • 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: 
kbnosurvey kbarchive kbcode kbhowto kbinterop KB141140
Delen
Extra ondersteuningsopties
Microsoft Community Support-forums
Neem rechtstreeks contact met ons op
Een door Microsoft gecertificeerde partner zoeken
Microsoft Store