why not, just return the same value in the update and insert routine - a 1 perhaps,
then check this value - if it's one, then it worked, if it's a zero - then it failed
or something like that.  if you need know if the user was inserted or updated, then
check for 1 - update; 2 - insert; if your return code is something other than those
two, then there was a problem.  you might also want to add some error checking:

IF EXISTS (SELECT USER_ID FROM AFLATE_AR 
            WHERE (USER_ID = @UserId)) 
  BEGIN 
    UPDATE AFLATE_AR 
       SET USER_EMAIL = @EMAIL, MODIFIED_DTM = GetDate(),
           MODIFIED_BY = 'admin' 
    WHERE 
       (USER_ID = @UserId) 

    IF @@ERROR <> 0
      RETURN -1
    ELSE
      RETURN 1 --let the caller know that we updated this user 
  END 
  ELSE 
  BEGIN 
    Insert into AFLATE_AR  VALUES ( 
      @UserId, 
      @UserName , 
      @UserEmail , 
      @AR_EMAIL , 
      @Create_dtm, 
      @Create_by, 
      getdate(), 
      'admin') 

    IF @@ERROR <> 0
      RETURN -1
    ELSE
      RETURN 2 -- let the caller know, we inserted 
 END 

hope this makes sense/helps,

john

--- dont worry <aspmailbox@y...> wrote:
> I want to add one more else and return code to the
> following code. 
> I want to know if the insert transaction worked or not
> with a return code of 3.  I tried else begin end , but
> i keep getting syntax error. 
> 
> CREATE PROCEDURE sp_rpUpdate 
> (@UserId int =NULL, 
> @UserName Varchar(80)=NULL, 
> @UserEmail Varchar(80)=NULL, 
> @EMAIL Varchar(80)=NULL, 
> @Create_dtm Varchar(80)=NULL, 
> @Create_by Varchar(80)=NULL 
> ) 
> AS 
> IF EXISTS (SELECT USER_ID FROM AFLATE_AR 
> WHERE (USER_ID = @UserId)) 
> BEGIN 
> UPDATE AFLATE_AR 
> SET USER_EMAIL = @EMAIL, MODIFIED_DTM = GetDate(),
> MODIFIED_BY = 'admin' 
> WHERE 
> (USER_ID = @UserId) 
> RETURN (1) 
> END 
> ELSE 
> BEGIN 
> Insert into AFLATE_AR  VALUES ( 
> @UserId, 
> @UserName , 
> @UserEmail , 
> @AR_EMAIL , 
> @Create_dtm, 
> @Create_by, 
> getdate(), 
> 'admin') 
> RETURN (0) 
> END 
> GRANT EXECUTE ON sp_Update TO PUBLIC 
> GO 
> '//i want to add it after the return(0) to know if
> insert worked or not? 
> 

John Pirkey 
MCSD 
John@S... 
http://www.stlvbug.org