Insert_Update Stored Procedure and returning Identity

• • Reply

  12 years ago

  by ansari.wajid

  WajidAnsari

  , India

  Joined 13 years ago
  Hi All I am writting an stored procedure to get the recently inserted records primary key as follows: ALTER PROCEDURE [DBO].[AddEditUserMsgDetails] ( @iJobID [INT]=NULL, @iUserID [INT]=NULL, @vFromName [VARCHAR](50)=NULL, @vFromAddress [VARCHAR](50)=NULL, @vSubject [VARCHAR](50)=NULL, @iListID [INT]=NULL, @vActualText [VARCHAR](100)=NULL ) AS BEGIN /* Logic Comments: INSERTING OR UPDATING INTO THE TABLE ezlMailJobs EXEC AddEditUserMsgDetails null,505,'waa','[email protected]','Test',816, 'Hi <!FN>
  please ignore' */ IF @iJobId IS NULL OR @iJobId=0 BEGIN INSERT INTO ezlMailJobs (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText) VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText) set @iJobID= @@Identity select @iJobID END IF @iJobId IS NOT NULL BEGIN UPDATE ezlMailJobs SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress, vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText WHERE iJobId=@iJobId END IF @@ERROR=0 BEGIN COMMIT TRAN SELECT 1 END ELSE BEGIN ROLLBACK TRAN SELECT -1 END As in the above stored procedure when ijobid is null or zero, It should execute insert statement and return recently inserted records ijobid else it should execute update statement. But as now it is executing insert statement and returning some garbage value. one more thing I have declared ijobid as identity. I am unable to figure out the problem. Please help me. Thanks WAA
  Report abuse