SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic Loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnph
Yak Posting Veteran

95 Posts

Posted - 05/02/2014 :  14:09:35  Show Profile  Reply with Quote
Hello,

I have a query that pulls many values and I want to loop through each value dynamically using a query.

SELECT COL1 FROM TABLE1


Results:
A
B
C
D

I want those results to loop and run this:

SELECT * FROM TABLE2 WHERE COL2 = A
SELECT * FROM TABLE2 WHERE COL2 = B
SELECT * FROM TABLE2 WHERE COL2 = C
SELECT * FROM TABLE2 WHERE COL2 = D



Edited by - Johnph on 05/02/2014 14:11:17

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/02/2014 :  16:09:55  Show Profile  Reply with Quote
Instead of looping, wouldn't your rather join the two tables and get the results you want like this?
SELECT t2.*
FROM 
	TABLE2 t2
	INNER JOIN TABLE1 t1
		ON t1.COL1 = t2.COL2;
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 05/03/2014 :  00:47:29  Show Profile  Reply with Quote
Hi John,
I don't know why you want to loop the results of Table1.AS james suggested you can use join concept to get the same result set .
Anyway here is the DYNAMIC LOOP ...

CREATE TABLE TABLE1 (Name VARCHAR(10))
CREATE TABLE TABLE2(ID INT,salary INT,Name VARCHAR(MAX))
INSERT INTO TABLE1 VALUES ('A'),('B'),('C'),('D'),('E'),('F')
INSERT INTO TABLE2 VALUES (1,100,'A'),(2,200,'B'),(3,300,'C'),(4,4000,'D'),(5,5000,'E'),(6,6000,'F')

DECLARE @Rn INT =1
DECLARE @Rn_Max INT =(SELECT COUNT(*) FROM TABLE1)
WHILE(@Rn<>@Rn_Max+1)
 BEGIN
	SELECT * FROM TABLE2 WHERE Name = (SELECT  Name FROM (SELECT Name,ROW_NUMBER()OVER(ORDER BY NAME) AS Rn  FROM TABLE1)a WHERE a.Rn = @Rn)
	SET @Rn = @Rn+1	
 END






---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000