Hi,
How to use WHILE in sql sub query. Please find the below example.
Here the @destinationBatchContent is contain more than 100 records. (Original table has 1 record is updating for all the @destinationBatchContent table)
When I am using WHILE loop in below I have facing error in below,
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Kindly let me if you know any.
Ex:
DECLARE @destinationBatchContent TABLE
( destinationBatchContentId INT
)
INSERT INTO @destinationBatchContent (destinationBatchContentId)
SELECT A.I_Batch_Content_Details_ID FROM dbo.T_Batch_Content_Details AS A INNER JOIN
dbo.T_Batch_Content_Details AS tbcd
ON(A.I_Batch_Content_Details_ID=tbcd.I_Batch_Content_Details_ID)
WHERE A.I_Batch_ID= @I_Interim_Batch_ID OR tbcd.I_Batch_ID = @I_Main_Batch_ID
DECLARE @Destination INT
SET @Destination=(SELECT * FROM @destinationBatchContent)
--The table is include morethan 100 records begin
WHILE (@Destination>0)
-- I need update all records from @destinationBatchContent table to dbo.T_Batch_Content_Details BEGIN
UPDATE tbcd
SET tbcd.S_Session_Alias = tbcd1.S_Session_Alias,
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
end
Karthik