Hi Everyone,
I need copy the data from Main batch to content Batch using sql stored procedure.
I have to mentioned the sp to incorporate following logic:
If child batch is empty ie no content exists in the child batch - you need to copy the main batch contents to the child batches.
If child batch has some content - you need to update those session content with you main batch content and insert rest of the main batch content in the child batch.
I want to alter the below sp for above mentioned logic. Kindly let me know if you know anything.
CREATE PROCEDURE [dbo].[uspCopyModuleBatchContent]
(
@I_Main_Batch_ID INT,
@I_Interim_Batch_ID INT,
@I_Term_ID INT,
@I_Module_ID INT,
@S_Crtd_By VARCHAR(20)=NULL,
@Dt_Crtd_On DATETIME=NULL
)
AS
BEGIN TRY
DECLARE @destinationBatchContent TABLE
( destinationBatchContentId INT
)
INSERT INTO @destinationBatchContent (destinationBatchContentId)
SELECT A.I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details AS A ,dbo.T_Batch_Content_Details AS tbcd
WHERE A.I_Batch_ID= @I_Interim_Batch_ID AND tbcd.I_Batch_ID = @I_Main_Batch_ID AND A.I_Term_ID = tbcd.I_Term_ID AND A.I_Module_ID = tbcd.I_Module_ID
IF EXISTS(SELECT * FROM @destinationBatchContent)
BEGIN
UPDATE tbcd
SET tbcd.S_Session_Alias = tbcd1.S_Session_Alias,
tbcd.S_Session_Chapter = tbcd1.S_Session_Chapter,
tbcd.S_Session_Description = tbcd1.S_Session_Description,
tbcd.S_Content_URL = tbcd1.S_Content_URL,
tbcd.I_Folder_Id = tbcd1.I_Folder_Id,
tbcd.S_Upd_By =@S_Crtd_By ,
tbcd.Dt_Upd_On = @Dt_Crtd_On,
tbcd.B_IsActive = tbcd1.B_IsActive
FROM dbo.T_Batch_Content_Details AS tbcd INNER JOIN
dbo.T_Batch_Content_Details AS tbcd1
ON tbcd.I_Term_ID = tbcd1.I_Term_ID
AND tbcd.I_Module_ID = tbcd1.I_Module_ID
WHERE tbcd.I_Batch_ID = @I_Interim_Batch_ID
AND tbcd1.I_Batch_ID=@I_Main_Batch_ID
--DELETE dbo.T_Batch_Content_Details WHERE I_Batch_Content_Details_ID IN (SELECT destinationBatchContentId FROM @destinationBatchContent)
END
ELSE
BEGIN
INSERT INTO T_Batch_Content_Details (I_Batch_ID,I_Folder_Id ,I_Term_ID,I_Module_ID, I_Session_ID, S_Session_Alias, S_Session_Chapter, S_Session_Description, S_Content_URL, S_Crtd_By, Dt_Crtd_On,B_IsActive,S_Session_Name,S_Session_Topic)
SELECT @I_Interim_Batch_ID,I_Folder_Id,I_Term_ID, I_Module_ID, I_Session_ID, S_Session_Alias, S_Session_Chapter, S_Session_Description, S_Content_URL, @S_Crtd_By, @Dt_Crtd_On,B_IsActive,S_Session_Name,S_Session_Topic
FROM dbo.T_Batch_Content_Details AS tbcd WHERE I_Batch_ID=@I_Main_Batch_ID AND I_Term_ID=@I_Term_ID AND I_Module_ID=@I_Module_ID
--SELECT @destinationBatchContentId= @@IDENTITY
END
END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Karthik