CODEWITHSUNDEEP
sqlsql-serverwhile-loop
Chuck
Chuck

Reputation: 1293

while loop select in SQL Server

I'm sure this is easy but I'm still getting syntax errors when I run it in SQL Server Management Studio v17.4.

I have table T1 which has serial numbers, and I just need to iterate through and check how many times they appear in T2. I tried like 

declare @serial int

select @serial = select serial from T1

while @serial
begin
    select count(*) from T2 where Serial = @serial
end

But I get an error:

Incorrect syntax near the keyword 'select'

How to do it? Thanks.

Upvotes: 0

Views: 4000

Answers (5)

Roshan
Roshan

Reputation: 31

Yes it is a syntax error


select @serial = select serial from T1; -- is wrong it should as written below

select @serial = serial from T1 ;

This will select the 1st value from Table T1.

This will remove the error but the query written will not yeald the required output.

You need to loop through table T1 and for each value of T1 search in T2.

See if the below simple query helps

select 
  serial, 
  count(Serial) as SerilaCount 
from 
  T1 
  inner join T2 on T1.serial = T2.Serial 
group by 
  T1.serial

Upvotes: 1

Brian Singleton
Brian Singleton

Reputation: 11

Seems like you could just do this in one quick statement, rather than a loop.

SELECT T2.Serial, Count(T2.Serial) as NumOfSerial
FROM T1 
    INNER JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T2.Serial

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Simply

   SELECT T1.Serial,
          COUNT(T2.Serial) AppearsInT2
    FROM T1 JOIN T2 ON T1.Serial = T2.Serial
    GROUP BY T1.Serial

There is no need to declare a variable or to use a loop.

If you want to return 0 for serials which not exists in the second table use LEFT JOIN instead

SELECT T1.Serial,
       COUNT(T2.Serial) AppearsInT2
FROM T1 LEFT JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T1.Serial;

Simple Demo

Upvotes: 2

Lamak
Lamak

Reputation: 70638

First of all, doing:

select @serial = select serial from T1

while @serial

…

Doesn't mean that it will start to magically loop for every value of serial that T1 has, it will just assign one value of serial to your variable (if it was correctly written anyway select @serial = serial from T1).

What you want doesn't really make sense to do it in a loop; sql works in sets, and you should try to write your code accordingly. In this case, a simple JOIN should do:

SELECT T1.Serial,
       ISNULL(COUNT(T2.Serial),0) N
FROM T1
LEFT JOIN T2
    ON T1.Serial = T2.Serial
GROUP BY T1.Serial
;

Upvotes: 1

Tim Mylott
Tim Mylott

Reputation: 2703

Instead of while loop, just join the tables and use an aggregate. Something like:

select a.serial, count(a.serial) from t2 a
inner join t1 b on b.serial = a.serial
group by a.serial

Without sample data I can't test it out for you, but that will perform a lot better for you.

Upvotes: 2

Related Questions