I have written following code to get data from starting table
select * from starting where PID in
(
select I.InstID from Inst I,
Trus T where I.TrusID=75 and T.TrusId= 75 and I.InstID = 54 and I.InstStatusID=1
)
Now I want to get "TrusID" and "InstID" dynamically to variable from another tables (lets say "Trus" and "Inst") and pass these as parameter to above query and run in a loop for each "TrusID" and "InstID" I got. How can I do this?
Without context on the bigger problem you're trying to solve, I'll venture an assumption that looping isn't needed.
Assuming the following DDL:
CREATE TABLE Trus ([TrusID] int);
INSERT INTO Trus ([TrusID]) VALUES (75);
CREATE TABLE Inst ([InstID] int, [InstStatusID] int, [TrusID] int);
INSERT INTO Inst ([InstID], [InstStatusID], [TrusID]) VALUES (54, 1, 75);
CREATE TABLE Starting ([PID] int);
INSERT INTO Starting ([PID]) VALUES (54);
The following SQL produces the same output as your original query:
SELECT S.*
FROM Starting AS S
INNER JOIN Inst AS I
ON S.PID = I.InstID
INNER JOIN Trus AS T
ON I.TrusID = T.TrusID
WHERE I.InstStatusID = 1;
Get in the habit of second guessing yourself if you've arrived at a procedural (loop/cursor) solution to a SQL problem.
