How To Return an Error Message from Microsoft SQL Server

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0
• Microsoft FoxPro for Windows, versions 2.6, 2.6a

SUMMARY

This article discusses how to create a stored procedure on Microsoft SQL Server to return a specific error message to the user. The Raiserror Command allows you to specify an error number and message. Note that numbers higher that 50,000 are not used by SQL Server. With Microsoft SQL Server 6.0, you can also write this procedure to the NT event log using the "with log" option.

MORE INFORMATION

Use the following code to implement the procedures above:

***************************************** *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 doesn't exist in the pubs database"
      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

REFERENCES

"Microsoft NT 3.51 Administrator Guide" and "Microsoft SQL Server Language Reference."

For more information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q141140
   TITLE     : How To Create a SQL Server Stored Procedure

   ARTICLE-ID: Q114787
   TITLE     : How To Execute a Stored Procedure on SQL Server