How to create a stored procedure in Microsoft SQL Server 2000 to return a specific error message to the user in Visual FoxPro

MORE INFORMATION

*****************************************
*SQL Stored Procedure Code on SQL Server*
*****************************************

   create procedure MyAuthors
   @theLastname varchar(25) as
   if not exists(select * from authors
   where au_lname = @theLastname)

   begin
     raiserror 50001 'This author does not exist in the pubs database" (#50001)'
      return
   end

   select * from authors
   where au_lname = @theLastname
				

***************************
*   Visual FoxPro Code    *
***************************

   CLEAR
   CLEAR ALL
   RELEASE ALL

   PUBLIC xHandle,ySuccess,zErr
   xHandle = SQLCONNECT("MySqlServer6","sa","")
   IF xHandle > 0
       ? "Good Connection"
       ? "Handle "+ str(xHandle)
    Else
       =MESSAGEBOX("Bad Connection",16,"Error Connecting")
       RETURN
   ENDIF

   ySuccess = SQLEXEC(xHandle,"use pubs")
   zErr=CheckErr()
   IF zErr = .F.
     RETURN
   ENDIF



   ySuccess = SQLEXEC(xHandle,"exec MyAuthors 'xxx'")
   * xxx above is surrounded by single quotes.
   zErr=CheckErr()
   IF zErr = .F.
      RETURN
   ENDIF

   RETURN

   PROCEDURE CheckErr
   IF ySuccess < 0
     * An error has occurred
     =AERROR(myError)

     CLEAR

      DO CASE
         CASE myError[1,5] = 50001
             *Predefined in SQL Stored Procedure MyAuthors
         xStrLoc =rat(']',myerror[1,3]) + 1
             * the left square bracket above is surrounded by single quotes
         =MESSAGEBOX(substr(myError[1,3],xStrLoc,55),16,"Author not found")
         =SQLDISCONNECT(xHandle)

      OTHERWISE
       *Handle other errors
      FOR n = 1 TO 7  && Display all elements of the array
         ? myError(n)
      ENDFOR
     =SQLDISCONNECT(xHandle)
     ENDCASE

     RETURN .F.

   ENDIF
   RETURN