I have one table tblLotTransfer structure is like this
Id FrLotNo ToLotNo MainLotNo LotUsed
1 22100001 22100003 22100001 1
2 22100001 22100004 22100001 1
3 22100001 22100005 22100001 1
4 22100002 22100006 22100002 1
5 22100003 22100007 22100001 0
1 st condition
Check if frLotNo 22100002 is not available in ToLotNo column then Insert mainlotNo same like frLotNo frLotNo = MainLotNo
2 condition
Check if frLotNo 22100003 is available in ToLotNo column then Insert mainlotNo where tuple found in row here we found 22100003 in first row so copy mainlono from that row and insert at row 5 and column mainLotNo
What I have tried:
I have write stored procedure for this but it does not satisfy second condition correctly. Can i use cursor for this?
ALTER PROCEDURE [dbo].[SP_LotTransfer_Save] @LId int = NULL,
@LDate datetime = NULL,
@LTransId int = NULL,
@ItemId int = NULL,
@LOperationId int = NULL,
@LFrLotNo varchar(20) = NULL,
@LToLotNo varchar(20) = NULL,
@MainLotNo varchar(20) = NULL,
@LTransferWt float = NULL,
@LIssuePr float = NULL,
@FrKarigarId int = NULL,
@ToKarigarId int = NULL,
@CreatedBy varchar(50),
@HIsOpening bit = 1,
@ActionType varchar(25)
AS
DECLARE @MaxLotNo varchar(20)
DECLARE @MainOLotNo varchar(20)
DECLARE @MaxId int
DECLARE @Rec_Cnt AS integer
DECLARE @Main_Cnt AS integer
BEGIN
SET NOCOUNT ON;
IF @ActionType = 'SaveData'
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT
@MaxId = ISNULL(MAX(LotTransferId), 0) + 1
FROM tblLotTransfer
SELECT
@MaxLotNo = ISNULL(MAX(MaxLotNo), 0) + 1
FROM vwGetMaxLotNo
PRINT @MaxLotNo
SET @LTransId = @LTransId + 1
SELECT
@Rec_Cnt = COUNT(ToLotNo)
FROM tblLotTransfer
WHERE ToLotNo IN (SELECT DISTINCT
FrLotNo tblLotTransfer
FROM tblLotTransfer
WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo)))
PRINT @Rec_Cnt
IF @Rec_Cnt = 0
BEGIN
INSERT INTO tblLotTransfer (LotTransferId, LotTransferDt, TransactionId, ItemId, OperationId, FrLotNo, ToLotNo, MainLotNo, TransferWt, IssuePr, FrKarigarId, ToKarigarId, CreatedBy, IsOpening)
VALUES (@MaxId, CONVERT(datetime, @LDate, 105), @LTransId, @ItemId, @LOperationId, @LFrLotNo, @MaxLotNo, @LFrLotNo, CAST(@LTransferWt AS float), CAST(@LIssuePr AS float), @FrKarigarId, @ToKarigarId, @CreatedBy, @HIsOpening)
END
SELECT
@Main_Cnt = COUNT(ToLotNo)
FROM tblLotTransfer
WHERE ToLotNo IN (SELECT DISTINCT
FrLotNo tblLotTransfer
FROM tblLotTransfer
WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo)))
IF @Main_Cnt > 0
BEGIN
UPDATE t
SET t.MainLotNo = (SELECT
MainLotNo
FROM tblLotTransfer
WHERE ToLotNo IN (SELECT DISTINCT
FrLotNo tblLotTransfer
FROM tblLotTransfer t2
WHERE FrLotNo = LTRIM(RTRIM(@LFrLotNo))))
FROM tblLotTransfer t
WHERE t.LotTransferId = @MaxId
END
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
INSERT INTO dbo.DB_Errors
VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE());
END
END CATCH
END
ELSE
END