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

Top 5 Contributors of the Month

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)
DECLARE @ErrorVar int
SET @ErrorVar = @ERROR
IF @ErrorVar <> 0 
               PRINT 'An Unknown Error Occurred'
               RETURN @ErrorVar

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:


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