Transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transaction

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-06 : 08:28:30

I have to write transactions…

Or fetch the present data and updating in another tables…

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[Table1]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric
)
as
Begin
INSERT into TrnDetGreySourcePlan
(
CompanyID,
DivisionID,
DepartmentID,
Orderno,
[Lineno],
StockType,
SerialNo,
BaseOrderno,
BaseLineno,
[FPS/TFSno],
Batchno,
AvailableQty,
AllottedQty
)
values(
@CompanyID,
@DivisionID,
@DepartmentID,
@OrderNo,
@LineNo,
@StockType,
@TAllotedQty,
@BOrderNo ,
@BLineNo,
@FTNo,
@BatchNumber,
@Available,
@Alloted

)
End

By this iam inserting the data in table1,
At the same time I have to update one of the field (Currentqty ) in other table having the same fields as table1 in row with Currentqty

Tabel2 fields-

CompanyID,
DivisionID,
DepartmentID,
Orderno,
[Lineno],
StockType,
SerialNo,
BaseOrderno,
BaseLineno,
[FPS/TFSno],
Batchno,
AvailableQty,
AllottedQty,
Currentqty = @Available - @Alloted(should be update) here with this value

At the same time I have to update another table3 which is also having some fields common with extra field qty

CompanyID,
DivisionID,
DepartmentID,
BaseLineno,
[FPS/TFSno],
Batchno,
AvailableQty,
qty = qty - @alloted

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 08:32:42

Are you using SQL 2005?

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-06 : 09:03:13

yes

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-06 : 09:05:38

Hi,

Try with this

Declare @errorId INT = NULL OUTPUT,
@errorMessage VARCHAR(200) = NULL OUTPUT,

BEGIN

BEGIN TRY

INSERT INTO Table1

UPDATE Tabl2

update Table3

END TRY

BEGIN CATCH

SELECT
@errorId = ERROR_NUMBER(),
@errorMessage = ERROR_MESSAGE()
RETURN -1

END CATCH
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 09:52:55

Try like this


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE [dbo].[Table1]
(
@CompanyID NVARCHAR(36), 
@DivisionID NVARCHAR(36), 
@DepartmentID NVARCHAR(36), 
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric
)
as 
Begin

DECLARE @CURRENT_INSERTED TABLE
(
CompanyID int, 
DivisionID int, 
DepartmentID int,
Orderno int,
[Lineno] int,
StockType varchar(10),
SerialNo int,
BaseOrderno int,
BaseLineno int,
[FPS/TFSno] int,
Batchno int,
AvailableQty,
AllottedQty
)
INSERT into TrnDetGreySourcePlan 
(
CompanyID, 
DivisionID, 
DepartmentID,
Orderno,
[Lineno],
StockType,
SerialNo,
BaseOrderno,
BaseLineno,
[FPS/TFSno],
Batchno,
AvailableQty,
AllottedQty
)
OUTPUT INSERTED.CompanyID,INSERTED.DivisionID,INSERTED.Orderno,
INSERTED.[Lineno],
INSERTED.StockType,
INSERTED.SerialNo,
INSERTED.BaseOrderno,
INSERTED.BaseLineno,
INSERTED.[FPS/TFSno],
INSERTED.Batchno,
INSERTED.AvailableQty,
INSERTED.AllottedQty
 INTO @CURRENT_INSERTED
values(
@CompanyID, 
@DivisionID, 
@DepartmentID,
@OrderNo,
@LineNo,
@StockType,
@TAllotedQty,
@BOrderNo ,
@BLineNo,
@FTNo,
@BatchNumber,
@Available,
@Alloted

)

UPDATE t
SET t.Currentqty=c.Available - c.Alloted
FROM Table2 t
INNER JOIN @CURRENT_INSERTED c
ON c.CompanyID=t.CompanyID
AND c.DivisionID=t.DivisionID
AND.... other fields


UPDATE t
SET t.qty=t.qty- c.Alloted
FROM Table3 t
INNER JOIN @CURRENT_INSERTED c
ON c.CompanyID=t.CompanyID
AND c.DivisionID=t.DivisionID
AND.... other fields
End

I have added all fields to table variable @CURRENT_INSERTED. But you need to add only those fields which distinguishes a unique record in each of tables and take join on those columns alone. Also make sure you put correct datatype in table definition (i have put them as int)

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-12 : 06:44:04

HI THANKS

--In need to update also:
--Actually:
--If passed @batchno is not there in TrnDetGreySourcePlan table then we have to insert (as no updation comes in this case)in TrnDetGreySourcePlan
--and update in InventorySerialNumbers as before,
--if passed @batchno is there in TrnDetGreySourcePlan then updation in TrnDetGreySourcePlan and as well as InventorySerialNumbers

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[PPUpdateStockDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate Datetime
)
as
Begin

DECLARE @Temp TABLE
(

AvailableQty Numeric,
AllottedQty Numeric,

)

UPDATE TrnDetGreySourcePlan
SET

[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
Batchno =@BatchNumber,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate

WHERE CompanyID = @CompanyID
AND DivisionID= @DivisionID
AND DepartmentID = @DepartmentID
AND OrderNO = @OrderNo

OUTPUT

UPDATED.AvailableQty,
UPDATED.AllottedQty,

INTO @TEMP
values
(

AvailableQty,
AllottedQty,

)

UPDATE ISN
SET ISN.CurrentLotOrderQty = T.AvailableQty - T.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP T
ON T.CompanyID=ISN.CompanyID
AND T.DivisionID=ISN.DivisionID
AND T.DepartmentID=ISN.DepartmentID
AND T.Batchno=ISN.SerialNumber
AND T.[FPS/TFSno]=ISN.PurchaseOrderNumber
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 07:33:35

quote:
Originally posted by Anushka

HI THANKS

--In need to update also:
--Actually:
--If passed @batchno is not there in TrnDetGreySourcePlan table then we have to insert (as no updation comes in this case)in TrnDetGreySourcePlan
--and update in InventorySerialNumbers as before,
--if passed @batchno is there in TrnDetGreySourcePlan then updation in TrnDetGreySourcePlan and as well as InventorySerialNumbers

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[PPUpdateStockDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate Datetime
)
as
Begin

DECLARE @Temp TABLE
(

AvailableQty Numeric,
AllottedQty Numeric,

)

UPDATE TrnDetGreySourcePlan
SET

[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
Batchno =@BatchNumber,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate

WHERE CompanyID = @CompanyID
AND DivisionID= @DivisionID
AND DepartmentID = @DepartmentID
AND OrderNO = @OrderNo

OUTPUT

UPDATED.AvailableQty,
UPDATED.AllottedQty,

INTO @TEMP
values
(

AvailableQty,
AllottedQty,

)

UPDATE ISN
SET ISN.CurrentLotOrderQty = T.AvailableQty - T.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP T
ON T.CompanyID=ISN.CompanyID
AND T.DivisionID=ISN.DivisionID
AND T.DepartmentID=ISN.DepartmentID
AND T.Batchno=ISN.SerialNumber
AND T.[FPS/TFSno]=ISN.PurchaseOrderNumber
End

Use a IF ELSE block to check if record with @BatchNo exists in TrnDetGreySourcePlan and do update else insert. Smething like

IF EXISTS(SELECT 1 FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNo)
BEGIN
Insert here
END
ELSE
BEGIN
update here
END

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-12 : 07:41:42

quote:
Originally posted by visakh16

quote:
Originally posted by Anushka

HI THANKS

--In need to update also:
--Actually:
--If passed @batchno is not there in TrnDetGreySourcePlan table then we have to insert (as no updation comes in this case)in TrnDetGreySourcePlan
--and update in InventorySerialNumbers as before,
--if passed @batchno is there in TrnDetGreySourcePlan then updation in TrnDetGreySourcePlan and as well as InventorySerialNumbers

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[PPUpdateStockDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate Datetime
)
as
Begin

DECLARE @Temp TABLE
(

AvailableQty Numeric,
AllottedQty Numeric,

)
UPDATE TrnDetGreySourcePlan
SET

[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
Batchno =@BatchNumber,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate

WHERE CompanyID = @CompanyID
AND DivisionID= @DivisionID
AND DepartmentID = @DepartmentID
AND OrderNO = @OrderNo

OUTPUT

UPDATED.AvailableQty,
UPDATED.AllottedQty,

INTO @TEMP
values
(

AvailableQty,
AllottedQty,

)

UPDATE ISN
SET ISN.CurrentLotOrderQty = T.AvailableQty - T.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP T
ON T.CompanyID=ISN.CompanyID
AND T.DivisionID=ISN.DivisionID
AND T.DepartmentID=ISN.DepartmentID
AND T.Batchno=ISN.SerialNumber
AND T.[FPS/TFSno]=ISN.PurchaseOrderNumber
End

Near those 2 lines it is showing error ...
Is it wring like this is correct ,As for Inserted we used?

UPDATED.AvailableQty,
UPDATED.AllottedQty,

Thanks.

Use a IF ELSE block to check if record with @BatchNo exists in TrnDetGreySourcePlan and do update else insert. Smething like

IF EXISTS(SELECT 1 FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNo)
BEGIN
Insert here
END
ELSE
BEGIN
update here
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 08:05:13

it should be

UPDATE  t 
SET 

[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
Batchno =@BatchNumber,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate
OUTPUT 

INSERTED.AvailableQty,
INSERTED.AllottedQty


INTO @TEMP
FROM TrnDetGreySourcePlan t
WHERE CompanyID = @CompanyID 
AND DivisionID= @DivisionID 
AND DepartmentID = @DepartmentID
AND OrderNO = @OrderNo

values
(

AvailableQty,
AllottedQty,

)

INSERTED is actually an internal temporary table used by SQL Server for update operation.

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 03:16:34

quote:
Originally posted by Anushka

quote:
Originally posted by Anushka

Hi,
I wrote like this :
Actually in Updation there is a problem:
It is showing ex.Message = "Violation of PRIMARY KEY constraint 'PK_TrnDetGreySourcePlan'. Cannot insert duplicate key in object 'dbo.TrnDetGreySourcePlan'.
The statement has been terminated."
while executing from front end...(.net)
I think here instead of updation insertion is gng on......In IF Block am dng updation...(Iam able to insert(else block is fine)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[PPUpdateStockDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@StockType NVARCHAR(36),
@FTNo NVARCHAR(36),
@BOrderNo NVARCHAR(36),
@BLineNo NVARCHAR(36),
@BatchNumber NVARCHAR(36),
@Available Numeric,
@Alloted Numeric,
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate Datetime
)
AS
BEGIN

IF EXISTS(SELECT * FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNumber)
BEGIN
DECLARE @Temp1 TABLE
(
CompanyID NVARCHAR(36),
DivisionID NVARCHAR(36),
DepartmentID NVARCHAR(36),
[FPS/TFSno] NVARCHAR(36),
Batchno NVARCHAR(36),
AvailableQty Numeric,
AllottedQty Numeric
)

UPDATE T
SET
CompanyID = @CompanyID,
DivisionID =@DivisionID,
DepartmentID =@DepartmentID,
[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
Batchno =@BatchNumber,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate

OUTPUT
INSERTED.CompanyID,
INSERTED.DivisionID,
INSERTED.DepartmentID,
INSERTED.[FPS/TFSno],
INSERTED.Batchno,
INSERTED.AvailableQty,
INSERTED.AllottedQty

INTO @TEMP1

FROM TrnDetGreySourcePlan T
WHERE T.CompanyID = @CompanyID
AND T.DivisionID= @DivisionID
AND T.DepartmentID = @DepartmentID
AND T.OrderNO = @OrderNo

UPDATE ISN
SET ISN.CurrentLotOrderQty = TP.AvailableQty - TP.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP1 TP
ON TP.CompanyID=ISN.CompanyID
AND TP.DivisionID=ISN.DivisionID
AND TP.DepartmentID=ISN.DepartmentID
AND TP.Batchno=ISN.SerialNumber
AND TP.[FPS/TFSno]=ISN.PurchaseOrderNumber
END

ELSE

BEGIN
DECLARE @Temp TABLE
(
CompanyID NVARCHAR(36),
DivisionID NVARCHAR(36),
DepartmentID NVARCHAR(36),
Orderno NVARCHAR(36),
[Lineno] NVARCHAR(36),
StockType NVARCHAR(36),
SerialNo NVARCHAR(36),
BaseOrderno NVARCHAR(36),
BaseLineno NVARCHAR(36),
[FPS/TFSno] NVARCHAR(36),
Batchno NVARCHAR(36),
AvailableQty Numeric,
AllottedQty Numeric,
EmployeeUserName NVARCHAR(36),
Trndate Datetime
)

INSERT INTO TrnDetGreySourcePlan
(
CompanyID,
DivisionID,
DepartmentID,
Orderno,
[Lineno],
StockType,
SerialNo,
BaseOrderno,
BaseLineno,
[FPS/TFSno],
Batchno,
AvailableQty,
AllottedQty,
EmployeeUserName,
Trndate
)

OUTPUT

INSERTED.CompanyID,
INSERTED.DivisionID,
INSERTED.DepartmentID,
INSERTED.Orderno,
INSERTED.[Lineno],
INSERTED.StockType,
INSERTED.SerialNo,
INSERTED.BaseOrderno,
INSERTED.BaseLineno,
INSERTED.[FPS/TFSno],
INSERTED.Batchno,
INSERTED.AvailableQty,
INSERTED.AllottedQty,
INSERTED.EmployeeUserName,
INSERTED.Trndate

INTO @TEMP
values
(
@CompanyID,
@DivisionID,
@DepartmentID,
@OrderNo,
@LineNo,
@StockType,
@TAllotedQty,
@BOrderNo ,
@BLineNo,
@FTNo,
@BatchNumber,
@Available,
@Alloted,
@EmployeeID,
@Trndate
)

UPDATE ISN
SET ISN.CurrentLotOrderQty = T.AvailableQty - T.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP T
ON T.CompanyID=ISN.CompanyID
AND T.DivisionID=ISN.DivisionID
AND T.DepartmentID=ISN.DepartmentID
AND T.Batchno=ISN.SerialNumber
AND T.[FPS/TFSno]=ISN.PurchaseOrderNumber

END
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 03:20:05

Are you still getting error? or did it work?

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 03:30:28

getting error while debugging from front end....
but while exicuting the SP it is successfully executed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 03:35:40

quote:
Originally posted by Anushka

getting error while debugging from front end....
but while exicuting the SP it is successfully executed

What is the error?

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 03:42:01

quote:
Originally posted by Anushka

It is showing ex.Message = "Violation of PRIMARY KEY constraint 'PK_TrnDetGreySourcePlan'. Cannot insert duplicate key in object 'dbo.TrnDetGreySourcePlan'.
The statement has been terminated."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 03:45:39

quote:
Originally posted by Anushka

quote:
Originally posted by Anushka

It is showing ex.Message = "Violation of PRIMARY KEY constraint 'PK_TrnDetGreySourcePlan'. Cannot insert duplicate key in object 'dbo.TrnDetGreySourcePlan'.
The statement has been terminated."

Thats obvious. You are trying to insert duplicate values into primary key of specified table. I guess itd CompanyID so you may need to check if a record with particular value of CompanyID exists before inserting it.

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 03:56:45

HI,
Here we are checking ryt.........
Actualy [Companyid,divisionid,departmentid,orderno,lineno,fps/tfs]-composit pk,batchno is PK's.
IF EXISTS(SELECT * FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNumber)
BEGIN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 04:21:01

quote:
Originally posted by Anushka

HI,
Here we are checking ryt.........
Actualy [Companyid,divisionid,departmentid,orderno,lineno,fps/tfs]-composit pk,batchno is PK's.
IF EXISTS(SELECT * FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNumber)
BEGIN

Nope. Here we are checking only if record with same batch number exists not one with passed on CompanyID. if it was
IF EXISTS(SELECT * FROM TrnDetGreySourcePlan WHERE CompanyID=@CompanyID)
BEGIN

then it would check whether companyid already exists which is what we want i guess. b/w am in right in assuming that Companyid is your PK?

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 04:26:13

Only Batchno differs in all rows..................
Batchno is PK,
Companyid,divisionid,departmentid,orderno,lineno,fps/tfs THESE ARE ALSO keys but no relations ....
Actualy iam having so many records ...,only batchno won't repeate (as this is PK)
But remaining all repeates....(Companyid,divisionid,departmentid,orderno,lineno,fps/tfs)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 04:34:19

i think update batch should be like this:-

IF EXISTS(SELECT * FROM TrnDetGreySourcePlan WHERE BatchNo=@BatchNumber)
BEGIN
DECLARE @Temp1 TABLE
(
CompanyID NVARCHAR(36), 
DivisionID NVARCHAR(36), 
DepartmentID NVARCHAR(36),
[FPS/TFSno] NVARCHAR(36),
Batchno NVARCHAR(36),
AvailableQty Numeric,
AllottedQty Numeric
)

UPDATE T
SET 
CompanyID = @CompanyID, 
DivisionID =@DivisionID, 
DepartmentID =@DepartmentID,
[Lineno] = @LineNo,
StockType=@StockType,
BaseOrderno = @BOrderNo,
BaseLineno = @BLineNo,
[FPS/TFSno] = @FTNo,
AvailableQty =@Available,
AllottedQty = @Alloted,
EmployeeUserName = @EmployeeID,
Trndate =@Trndate


OUTPUT 
INSERTED.CompanyID, 
INSERTED.DivisionID, 
INSERTED.DepartmentID,
INSERTED.[FPS/TFSno],
INSERTED.Batchno,
INSERTED.AvailableQty,
INSERTED.AllottedQty

INTO @TEMP1

FROM TrnDetGreySourcePlan T
WHERE Batchno =@BatchNumber,



UPDATE ISN
SET ISN.CurrentLotOrderQty = TP.AvailableQty - TP.AllottedQty
FROM InventorySerialNumbers ISN
INNER JOIN @TEMP1 TP
ON TP.CompanyID=ISN.CompanyID
AND TP.DivisionID=ISN.DivisionID
AND TP.DepartmentID=ISN.DepartmentID
AND TP.Batchno=ISN.SerialNumber
AND TP.[FPS/TFSno]=ISN.PurchaseOrderNumber
END 
....

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-13 : 04:42:56

YES GOT IT...............
Thanks instead of Batchno ...i kept orderno...........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources