How to get next record in sql sp? [Resolved]

hi karthik,
Check the below solution.. It will loop through each Interim batch id . assumed that Batch Content Details Id is single..
NOTE: I'm using CURSOR for this scenario.. If you explain your scenario with sample data and expected output.. I can provide you the solution without using CURSOR....
--Check the solution once... and let me know the status

DECLARE @Inter_batch_ID INT



DECLARE db_cursor CURSOR FOR 

SELECT I_Interim_batch_ID FROM dbo.T_Interim_Batch_Map AS tibm WHERE I_Main_Batch_ID = @I_Batch_ID -- Here getting 2 or more records, Rightnow it is getting one time. 

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @Inter_batch_ID 

WHILE @@FETCH_STATUS = 0   

BEGIN

	DECLARE @destinationBatchContentId1 INT 

	IF(@I_Session_ID IS NOT NULL) 

	BEGIN 

		 -- If there is only one I_Batch_Content_Details_ID for the condition

		IF EXISTS (SELECT I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details WHERE I_Batch_ID = @Inter_batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND I_Session_ID = @I_Session_ID) 

		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 = tbcd1.S_Crtd_By

				,tbcd.Dt_Upd_On = tbcd1.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 AND tbcd.I_Session_ID=tbcd1.I_Session_ID 

			WHERE tbcd.I_Batch_ID = @Inter_batch_ID 

			AND tbcd1.I_Batch_ID=@I_Batch_ID 

		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) 

			VALUES(@Inter_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,@IsActive,@S_Session_Name,@S_Session_Topic) 

		END 

	END

	FETCH NEXT FROM db_cursor INTO @Inter_batch_ID 

END 

CLOSE db_cursor   

DEALLOCATE db_cursor 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @Inter_batch_ID INT 

SELECT @Inter_batch_ID = I_Interim_batch_ID FROM dbo.T_Interim_Batch_Map AS tibm WHERE I_Main_Batch_ID = @I_Batch_ID // Here getting 2 or more records, Rightnow it is getting one time. 

Hi Karthik,
The above code will always return last I_Interim_batch_ID in the dbo.T_Interim_Batch_Map table...

Can you explain the scenario which you are trying to do in the above T-SQL script....
I will provide you the solution...
I can figure out some logical errors in your script too

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu,

This is a copy session.
For Ex, I have 2 batches name as A, B.
A is a main batch (having 10 content)
B is a child of A batch(having 10 content)
B batch is already mapped in to the C and D batch(C,D also having 10 content)

Now I add one content in the main batch A(like 11th content)
Now we want copy from A to B
B batch is copied properly (Added 11th content from A).
But the B batch has already mapped in the C and D na.
We want automatically copied the 11 content in the batch C and D.

Note : content is like Topic, Description Date,...
Whenever we have mapped from some batch to another batch(here only insert the data dbo.T_Interim_Batch_Map)

I posted full sp in below,
ALTER PROCEDURE [dbo].[uspSaveInOtherBatches]
(
@I_Batch_Content_Details_ID INT,
@I_Batch_ID INT,
@I_Term_ID INT,
@I_Module_ID INT,
@I_Session_ID INT,
@S_Session_Alias VARCHAR(250),
@S_Session_Chapter VARCHAR(250),
@S_Session_Description VARCHAR(500),
@S_Content_URL VARCHAR(500),
@S_Crtd_By VARCHAR(20)=NULL,
@Dt_Crtd_On DATETIME=NULL,
@iSrcBatchContentId INT,
@IsActive BIT ,
@I_Folder_Id INT,
@S_Session_Name VARCHAR(500),
@S_Session_Topic VARCHAR(500)
)
AS
BEGIN TRY
DECLARE @destinationBatchContentId INT
IF(@I_Session_ID IS NOT NULL)
BEGIN
SELECT @destinationBatchContentId = I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details WHERE I_Batch_ID = @I_Batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND I_Session_ID = @I_Session_ID
IF(@destinationBatchContentId > 0)
BEGIN
UPDATE dbo.T_Batch_Content_Details SET S_Session_Alias = @S_Session_Alias,S_Session_Chapter = @S_Session_Chapter,S_Session_Description = @S_Session_Description,S_Content_URL = @S_Content_URL,I_Folder_Id = @I_Folder_Id, S_Upd_By = @S_Crtd_By, Dt_Upd_On = @Dt_Crtd_On, B_IsActive = @IsActive
WHERE I_Batch_ID = @I_Batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND I_Session_ID = @I_Session_ID
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)
VALUES(@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,@IsActive)

SELECT @destinationBatchContentId= @@IDENTITY
END
END
ELSE
BEGIN
SELECT @destinationBatchContentId = I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details WHERE I_Batch_ID = @I_Batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND S_Session_Name = @S_Session_Name AND S_Session_Topic = @S_Session_Topic
IF(@destinationBatchContentId > 0)
BEGIN
UPDATE dbo.T_Batch_Content_Details SET S_Session_Alias = @S_Session_Alias,S_Session_Chapter = @S_Session_Chapter,S_Session_Description = @S_Session_Description,S_Content_URL = @S_Content_URL,I_Folder_Id = @I_Folder_Id, S_Upd_By = @S_Crtd_By, Dt_Upd_On = @Dt_Crtd_On, B_IsActive = @IsActive
WHERE I_Batch_ID = @I_Batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND S_Session_Name = @S_Session_Name AND S_Session_Topic = @S_Session_Topic
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)

VALUES(@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,@IsActive,@S_Session_Name,@S_Session_Topic)

SELECT @destinationBatchContentId= @@IDENTITY
END
END
--Update Cue Points from the source Batch
DELETE FROM dbo.T_Session_Content_CuePoint_Details WHERE I_Batch_Content_Details_ID = @destinationBatchContentId
INSERT INTO dbo.T_Session_Content_CuePoint_Details
( I_Batch_Content_Details_ID ,
S_Topic_Name ,
dt_Time ,
S_Crtd_By ,
Dt_Crtd_On
)
SELECT @destinationBatchContentId,S_Topic_Name,dt_Time,@S_Crtd_By,@Dt_Crtd_On FROM dbo.T_Session_Content_CuePoint_Details WHERE I_Batch_Content_Details_ID = @iSrcBatchContentId


--Added by KK
DECLARE @Inter_batch_ID INT
SELECT @Inter_batch_ID = I_Interim_batch_ID FROM dbo.T_Interim_Batch_Map AS tibm WHERE I_Main_Batch_ID = @I_Batch_ID
IF EXISTS(SELECT @Inter_batch_ID)-- FROM dbo.T_Interim_Batch_Map WHERE I_Main_Batch_ID = 5446)
BEGIN
DECLARE @destinationBatchContentId1 INT
IF(@I_Session_ID IS NOT NULL)
BEGIN
SELECT @destinationBatchContentId1 = I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details WHERE I_Batch_ID = @Inter_batch_ID AND I_Term_ID = @I_Term_ID
AND I_Module_ID = @I_Module_ID AND I_Session_ID = @I_Session_ID
IF(@destinationBatchContentId1 > 0)
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 = tbcd1.S_Crtd_By, tbcd.Dt_Upd_On = tbcd1.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 --,@InterimBatch AS ib
ON tbcd.I_Term_ID = tbcd1.I_Term_ID AND tbcd.I_Module_ID = tbcd1.I_Module_ID AND tbcd.I_Session_ID=tbcd1.I_Session_ID--AND tbcd.S_Session_Name = tbcd1.S_Session_Name --AND tbcd.S_Session_Topic = tbcd1.S_Session_Topic
WHERE tbcd.I_Batch_ID = @Inter_batch_ID--IN (SELECT * FROM @InterimBatch)
AND tbcd1.I_Batch_ID=@I_Batch_ID
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)
VALUES(@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,@IsActive,@S_Session_Name,@S_Session_Topic)
END
END
END
--End By KK
END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

1) Get comma separated I_Interim_batch_IDs based on I_Main_Batch_ID = @I_Batch_ID
2) pass one by one interim batch id and do remaining operation

I will provide u alternate solution if possible and I have free time


EDIT:
Is it OK to perform same operation on matched records based on set-approach?
I mean to say that can we do same operation based on match-records(set-based) without going through each interim batch id

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Ok Chandu.

Thanks

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi Karthik,
Do you have multiple I_Batch_Content_Details_IDs for the below query?

SELECT @destinationBatchContentId1 = I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details WHERE I_Batch_ID = @Inter_batch_ID AND I_Term_ID = @I_Term_ID AND I_Module_ID = @I_Module_ID AND I_Session_ID = @I_Session_ID 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu,

Yes.
User has select multiple batches, it is getting multiple I_Batch_Content_Details_IDs.
Most of the time user has select only one batch, then it is getting only one I_Batch_Content_Details_IDs.

Thanks

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi Chandu,

Thanks a lot. It is working properly. Thanks again for your valuable help.

Thanks

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Hi karthik,
You are welcome...
If you have any issue regarding SQL server, post us here with sample data and expected output with proper explanation then you doesn't need to wait for response long time...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator