C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

Handling error in stored procedure

Posted Date: 23 Sep 2008 Resource Type: Articles Category: Databases

Posted By: Shashi Member Level: Gold
Rating:

Points: 7

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.

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

Handling error in stored procedure .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Calling stored procedure with Parameters
	Previous Resource: How to delete or change a stored procedure?
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

doors in nj