SQL Server / 2 select in the same Stored procedure

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?开发者_开发百科

---

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)

---

Try this:

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

---

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.

---

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