Insert_Update Stored Procedure and returning Identity

sql server , asp.net , csharp Mahbūbnagar, India
  • 8 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','waa@v-empower.com','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

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger