SQL Server / 2 select in the same Stored procedure

Question

Here's what I'm trying do to in a single SQL Server procedure:

@ID1 int

select ID2 from TableA where ID1 = @ID1

(for each selected @ID2)
    select * from TableB where ID2 = @ID2

Any ideas?

Answer 1

Try this:

select * from TableB b where exists (select top 1 * from TableA a where a.ID2 = b.ID2)

Answer 2

That can be done in a single statement:

SELECT b.*
  FROM TABLE_B b
  JOIN TABLE_A a ON a.id2 = b.id2
 WHERE a.id1 = @ID1

But this means that there will be duplicates if more than one record in TABLE_A relates to a TABLE_B record. In that situation, use EXISTS rather than adding DISTINCT to the previous query:

SELECT b.*
  FROM TABLE_B b
 WHERE EXISTS(SELECT NULL
                FROM TABLE_A a
               WHERE a.id2 = b.id2
                 AND a.id1 = @ID1)

The IN clause is equivalent, but EXISTS will be faster if there are duplicates:

SELECT b.*
  FROM TABLE_B b
 WHERE b.id2 IN (SELECT a.id2
                   FROM TABLE_A a
                  WHERE a.id1 = @ID1)

Answer 3

SELECT * 
FROM TableB 
WHERE ID2 IN (SELECT ID2 FROM TableA WHERE ID1 = @ID1)

Generally speaking, you don't want to do any kind of looping in SQL Server. Try using "Set based" operations.

Answer 4

For each doesnt work in sql, you must use cursors.

declare @id int declare @id2 int

declare mycursor cursor for select id2 from tablea where id=@id

open mycursor

fetch next from mycursor into @id2

while @@fetch_status = 0 begin

your code here

fetch next from mycursor into @id2 end close mycursor deallocate mycursor