June 6, 2013 at 11:10 pm
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
June 7, 2013 at 12:02 am
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.
June 7, 2013 at 12:31 am
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
June 7, 2013 at 2:46 am
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.
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