Microsoft KB Archive/151090

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

Article ID: 151090

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b 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

-



This article was previously published under Q151090



SUMMARY
This article describes how to create a stored procedure in Microsoft SQL Server 2000 to return a specific error message to the user. The Raiserror command lets you specify an error number and a message. You can also write this procedure in the NT event log by using the "with log" option in Microsoft SQL Server 6.0.



MORE INFORMATION
Use the following code to implement the previous procedures. Run this code in the Query Analyzer tool for SQL Server. *****************************************
 * 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

To call the SQL server stored procedure, create a program to call Proc_test.prg. Add the following code. Run the program file in Microsoft Visual FoxPro.

***************************
 * 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

