0

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','waa@v-empower.com','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

2
Contributors
1
Reply
3
Views
8 Years
Discussion Span
Last Post by dickersonka
0

first you are missing an else on your first if checking jobid

after the insert use,

return @@IDENTITY

from codeside add a parameter with

ParameterDirection.ReturnValue;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.