2
Answers

Insert_Update Stored Procedure and returning Identity

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,
   '<html><body>Hi &lt;&#33;FN&gt;<br>please ignore</body></html></body></html>'

*/

  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

Answers (2)