Anytime you exit a stored procedure, the exit should have a RETURN(n) or RETURN n.
On success, return 0. On error, return some non-zero value.
In error checking code, there is a specific error that should be returned.
Example Code:
SELECT
@error = @@error,
@rowcount = @@rowcount
IF @error <> 0 BEGIN
EXEC dbo.TagValueList @list OUTPUT, 'UserID', @UserID, 'PurchaseID', @purchaseID
RAISERROR(990002, 16, 1, @error, @spName, 'RGExampleTable', @list)
RETURN(@error)(Note here that @error [single '@'] is returned because it is the non-zero error returned from the database. Note also that the RAISERROR call does not include the WITH SETERROR option because the error code is not used in the RETURN.) END
ELSE IF @rowcount <> 1 BEGIN
EXEC dbo.TagValueList @list OUTPUT, 'UserID', @UserID, 'PurchaseID', @purchaseID
RAISERROR(990006, 16, 1, @spName, 'RGExampleTable', @rowcount, @list) WITH SETERROR
RETURN(@@error)(Note here that @@error [double '@'] is returned as a convention. Any non-zero number could be returned. @@error after a RAISERROR WITH SETERROR will return the number of the error used in RAISERROR. In this case, the value of @@error will be 990006.) END