>Actually, I handle errors in my Data Access class, so there is no need for me to do it directly in the SP. I am open to the suggestion though, for those who wish to do it differently- do you have examples of how you handle errors in your SPs?

Eric,

Here's an example of a DELETE SP that checks for a foreign key constraint violation and then any other errors:

CREATE PROCEDURE usp_community_delete
@nCommunID int,
@nResultCode int=0 OUTPUT
AS

DECLARE @nError int

-- Delete the row.
DELETE FROM community WHERE communid = @nCommunID
SET @nError = @@ERROR

-- Determine if a foreign key constraint has been violated.
IF @nError = 547
BEGIN
RAISERROR('This community is already in use and cannot be deleted.',11,1)
GOTO err_handler
END

-- Check for any other non-fatal errors.
IF @nError <> 0
BEGIN
RAISERROR('Unable to delete this community.',11,1)
GOTO err_handler
END

-- The DELETE was successful.
RETURN @nResultCode

-- Error Handler
err_handler:
SET @nResultCode = -1003
RETURN @nResultCode


I typically check the @@ERROR function after every SQL statement. If an error occurs, the message can can be retrieved using AERROR() in my data service object (have to do some parsing.)