ALTER PROCEDURE [dbo].[JOBRECINSERT]

@JOBID as bigInt,

@YEARMONTH as int,

@TRANSTYPE as char,

AS

BEGIN

DECLARE @ERNO AS INT

BEGIN

SET @ERNO=(SELECT EID FROM TABLEA where JOBID=@JOBID)

SET @ERNO=@ERNO+1 -- this no shld be genereated only once

END

BEGIN

INSERT INTO TABLEB(JOBID,YEARMONTH,TRANSTYPE,EID)

VALUES(@JOBID,@YEARMONTH,@TRANSTYPE,@ERNO)

END

begin

SET @ERNO=0

SET @ERNO=(SELECT MAX(EID) FROM TABLEB WHERE JOBID=@JOBID)

UPDATE TABLEA SET EID=@ERNO WHERE JOBID=@JOBID

end