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 <!FN><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
Unsolved 
