SP for inserting max(eid)+1 no for all fields

  • tableA has eid field which stores the last eid value from tableB when records are added

    in tableB records are saved from the application thr' stored procedure

    stored procedure has insert statemnet for inserting records in tableB , while inserting the records i need the eid field value as below

    AS

    BEGIN

    DECLARE @EID AS INT

    BEGIN

    set @EID=select max(eid) from tableA

    set @EID=@EID+1

    end

    begin

    insert statement

    end

    begin

    set @EID=select max(eid) from tableB

    update tableA set eid=@EID

    end

    suppose the max eid no in tableA is 10 .

    5 records are inserted in tableB i need eid field in tableB as 11 for all 5 records &

    eid field in tableA shld be set to 11

    but currently i am getting eid as serial no .. as 11,12,12,14,15

  • Unfortunately you haven't provided enough information for anyone to try and help you. We really need to see the entire procedure plus DDL for the tables involved, sample data for the tables, sample data for the test update/insert to the tables, and the expected results when all is done based on the sample data.

  • 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

  • It seems you are calling the procedure 5 times for the 5 rows

    You will have to change this approach and call the procedure only once and insert all the 5 rows at once.

    Do you have any staging table where you are storing these 5 rows before inserting them into the main table?

    Edit:Corrected spelling mistake.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply