.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Handling error in stored procedure

Posted By:Shashi Ray       Posted Date: September 22, 2008    Points: 10    Category: DataBase    URL: http://www.dotnetspark.com  
 

 

 

Handling error in stored procedure

In addition to passing output parameters back to the calling program, stored procedures also pass back a return value. By default, this value is zero. If an error occurs during the execution of a stored procedure you may want to pass a value back to the calling environment that indicates the error that occurred. To do that you use the RETURN statement and the @@ERROR function.

The @@ERROR system function returns the error number that's generated by the execution of the most recent SQL statement. If the value is zero, it means that no error has occurred. The stored procedure listed below uses this function to test whether a DELETE statement that deletes a row from authors table is successful.

CREATE PROC spDeleteShashi @FirstName varchar(50)
As
DECLARE @ErrorVar int
DELETE FROM SHASHI WHERE fn_name = @FirstName
SET @ErrorVar = @ERROR
IF @ErrorVar <> 0 
        BEGIN
               PRINT 'An Unknown Error Occurred'
               RETURN @ErrorVar
        END


RETURN statement immediately exists the procedure and returns an optional integer value to the calling environment. If you don't specify the value in this statement the return value is zero.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend