Gehima2016 avatar image
Gehima2016 asked

While Loop syntax

Please, help check this query; DECLARE @cnt INT = 0 DECLARE @strEmployeeID varchar(50) = ( Select * --[LANID] from [dbo].[tblEmployee] where [blnTerminationComplete] = 0 and[TerminationDate] is not null ) WHILE @cnt < ( (select count (Distinct [strDirectSupervisor]) from [TEProd].[dbo].[tblEmployeeTE] where [strDirectSupervisor] is not null) ) BEGIN ( SELECT c.strDirectSupervisor AS Manager FROM [dbo].[tblEmployee] a LEFT OUTER JOIN [TEProd].[dbo].[tblEmployeeTE] c on a.[LANID] COLLATE DATABASE_DEFAULT = c.[strEmployeeID] where c.[strEmployeeID] = 'MQ67' and [blnTerminationComplete] = 0 and[TerminationDate] is not null ) SET @cnt = @cnt + 1; END;
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Gehima2016 It is not clear what you would like to achieve. So far, it looks like you compare the value of the @cnt variable with the count of supervisors and then issue a select statement as many times as necessary until the value of @cnt variables reaches the count of supervisors. So, if there are, say, 50 supervisors, your loop will produce and execute (?!) 50 identical select statements. Each of the select statements has a flaw because you are using LEFT join to [dbo].[tblEmployeeTE] c and then require that c.[strEmployeeID] = someValue, but this ***should never happen*** with left join, i.e. you cannot put anything concerning the right table's info into the WHERE clause because this effectively turns your LEFT JOIN into INNER join. If this is the case then why do you even use LEFT JOIN if you don't even care to get the results expected from the LEFT join? If what I am trying to point out is not clear then please let me know and I will come up with example showing the problem. Also, please clarify what is that you would like to happen in your WHILE loop. Thank you.
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.