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)
                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;

more ▼

asked Jan 11 at 07:05 PM in Default

avatar image

1 1 5

@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.

Jan 11 at 09:05 PM Oleg
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 11 at 07:05 PM

Seen: 47 times

Last Updated: Jan 12 at 02:11 PM

Copyright 2018 Redgate Software. Privacy Policy