How to use WHILE in sql sub query.

Posted by Karthik2010_Mca under Sql Server on 7/27/2013 | Points: 10 | Views : 3482 | Status : [Member] | Replies : 4
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


Responses

Posted by: Ssj_Kumar on: 7/28/2013 [Member] Starter | Points: 25

Up
0
Down
Replace
DECLARE @Destination INT
SET @Destination=(SELECT * FROM @destinationBatchContent) --The table is include morethan 100 records

with

DECLARE @Destination INT
SET @Destination=(SELECT COUNT(destinationBatchContentId) FROM destinationBatchContent)

Regards,
Jayakumar Selvakani

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

Posted by: Karthik2010_Mca on: 7/28/2013 [Member] Starter | Points: 25

Up
0
Down

Hi,

It is working fine. But it is updating multiple times.
Kindly let me know the below loop condition is correct.

WHILE (@Destination>0)
The @Destination is contain primary key, Ex. 1 to 300

Thanks

Karthik

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

Posted by: Ssj_Kumar on: 7/29/2013 [Member] Starter | Points: 25

Up
0
Down
In your case while is not required, just use IF that is enough

if(@Destination>0)
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

Regards,
Jayakumar Selvakani

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

Posted by: Bandi on: 7/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Why you need declaration & insertion of records again. You can count the number of records as follows:
DECALRE @Destination int = 0;
SELECT @Destination = COUNT(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

Instead of the following lengthy code you can use the above single SQL Query and then use Jayakumar's UPdate statement by checking @destination >0
/*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 */

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

Login to post response