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;
@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.
18 People are following this question.
