insert_update Stored procedure and return Identity value

wajans

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

Ashfield

If the jobid is null or 0 you are executing an insert AND an update

IF @iJobId IS NULL OR @iJobId=0
BEGIN
..do insert
set @iJobID= @@Identity
select @iJobID
END
IF @iJobId IS NOT NULL ------ YOU SET IT 2 LINES ABOVE
BEGIN
...do update etc

Also you have commit/rollback, but I don't see a begin tran? Hope this helps

Bob Ashfield Consultants Ltd

wajans

Thanks for your reply I found the solution, want to share with the needful.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [DBO].[AddEditUserMsgDetails]
(
@iJobID [NUMERIC]=NULL,
@iUserID [NUMERIC]=NULL,
@vFromName [VARCHAR](50)=NULL,
@vFromAddress [VARCHAR](50)=NULL,
@vSubject [VARCHAR](50)=NULL,
@iListID [NUMERIC]=NULL,
@vActualText [TEXT]=NULL
)
AS
BEGIN
/* Written By: Wajid Ahmed Ansari
Date: 05-11-2008
Logic Comments: INSERTING OR UPDATING INTO THE TABLE ezlMailJobs
EXEC AddEditUserMsgDetails null,505,'waa','waa@v-empower.com','Test',816,'Hi <!FN>
please ignore
'
EXEC AddEditUserMsgDetails NULL,505,'Khadri','khadr.syed@v-empower.com','Testing Procedure',NULL,NULL
EXEC AddEditUserMsgDetails 4,505,'Khadri','khadr.syed@v-empower.com','Testing Procedure',816,NULL
SELECT * FROM ezlMailJobs
sp_help ezlMailJobs
*/
SET NOCOUNT ON
DECLARE @Return [NUMERIC]
SET @Return=NULL
BEGIN TRAN
IF @iJobId IS NULL OR @iJobId=0
BEGIN
INSERT INTO table1 (iUserID,vFromName,vFromAddress,vSubject,iListID,vActualText)
VALUES (@iUserID,@vFromName,@vFromAddress,@vSubject,@iListID,@vActualText)
SET @Return= SCOPE_IDENTITY()
SELECT @Return AS iJobId
END
IF @iJobId IS NOT NULL
BEGIN
UPDATE table1 SET iUserID=@iUserID,vFromName=@vFromName,vFromAddress=@vFromAddress,
vSubject=@vSubject,iListID=@iListID,vActualText=@vActualText
WHERE iJobId=@iJobId
SELECT 0
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
SET NOCOUNT OFF
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Ben Fair

The best way to do this, in my opinion, is to use the SCOPE_IDENTITY() function. This function will return the most recent identity value created in the current scope. Returning the value for the current scope is important because if you have triggers firing that perform inserts the @@IDENTITY variable will return the last identity value created, even if it was from a different table because of a trigger. You should use: set @iJobID = SCOPE_IDENTITY() after the insert.

Keep It Simple Stupid! (KISS)

wajans

Thanks a lot. Its working now.