Dynamic Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Loop

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2014-05-02 : 14:09:35

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-02 : 16:09:55

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;

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-03 : 00:47:29

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.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources