Trying to build table with While loop in SQL

Hello and thank you for taking a moment. I am trying to build a  table by looping through the contents of an existing table.  Once built I try to select from the newly created table (@BPRLIKELISTRESULTS)  but sql server gives the following error:

[Msg 4145, Level 15, State 1, Line 32
An expression of non-boolean type specified in a context where a condition is expected, near 'SELECT'.


Line 32 is the select statement at the bottom of my query here below. If there is any advice as to how to solve this , it would be greatly appreciated.

DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     int

DECLARE @BPRLIKELISTRESULTS TABLE ( Value varchar(20))



DECLARE @BPRLIKELIST TABLE (RowID int not null primary key identity(1,1), Listitem varchar(20))  

INSERT into @BPRLIKELIST (Listitem)
SELECT Value
FROM         OlisUserFilters
WHERE     (ReqTitle = 'BPR') and (Active = 1) and (Operator = 'Like')  and (UserID = 3183)
 
Select * From @BPRLIKELIST

SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
     SET @CurrentRow=@CurrentRow+1
 
     Insert into @BPRLIKELISTRESULTS (Value)
     Select Distinct officeLocation
     from VPSI_Driver
     Where (Select Listitem from @BPRLIKELIST where RowID=@CurrentRow)
     
End


SELECT *
FROM @BPRLIKELISTRESULTS
jazzcatoneAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
I am not convinced this should be in a WHILE loop; however, the issue is that you likely want a JOIN, EXISTS, or something connecting the sub-query to the outer query. As the error indicates, you are simply calling a sub-query within the WHERE clause -- that is not a valid WHERE condition.

EDIT: Does the VPSI_Driver table have a column that associates to Listitem (i.e., the [Value] column of OlisUserFilters)?

In other words, you may be able to do something as simple as this:
SELECT DISTINCT VPSI_Driver.officeLocation
FROM OlisUserFilters
JOIN VPSI_Driver 
  ON VPSI_Driver.Listitem_Equivalent_Column = OlisUserFilters.[Value]
WHERE (OlisUserFilters.ReqTitle = 'BPR') 
  AND (OlisUserFilters.Active = 1) 
  AND (OlisUserFilters.Operator = 'Like')  
  AND (OlisUserFilters.UserID = 3183)
;

Open in new window

0
 
Anoo S PillaiConnect With a Mentor Commented:
The issue within the code is at :-

 Insert into @BPRLIKELISTRESULTS (Value)
     Select Distinct officeLocation
     from VPSI_Driver
     Where (Select Listitem from @BPRLIKELIST where RowID=@CurrentRow)

The where caluse should be having a column name and operator before the subquery , say like Where <VPSI_Driver_Column> IN  (Select Listitem from @BPRLIKELIST where RowID=@CurrentRow)
0
All Courses

From novice to tech pro — start learning today.