CODEWITHSUNDEEP
sqlsql-serverloops
Pavi
Pavi

Reputation: 345

How to use for loop scenario in sql server

I am getting multiple values from my query. 

select l_t_id from MLTalukDetails join MLDistrictDetails on MLTalukDetails.L_D_Id=4

It returns 13,14,15,16. Now I want to take each of these values at a time and get the details from those values i.e.

select Sc_Id from MSchoolDetails 
where 
MSchoolDetails.L_T_Id=@talukid and 
MSchoolDetails.Y_Id=@yearid

What can i do this scenario. How can I use the for loop syntax here?

Upvotes: 1

Views: 2164

Answers (2)

Akash KC
Akash KC

Reputation: 16310

Use cursor if you want to use loop....Here is workaround for you which I have implemented in stored procedure....

CREATE PROCEDURE [dbo].[proc_GetSc_Id]
@yearid INT
AS
CREATE TABLE #Sc_Id(Sc_Id varchar(20))
BEGIN       
DECLARE @talukid varchar(20)

    declare c cursor For 
    select l_t_id from MLTalukDetails join MLDistrictDetails on MLTalukDetails.L_D_Id=4

open c
    fetch next from c into @talukid
    while(@@FETCH_STATUS=0)
    BEGIN
        insert into #Sc_Id(Sc_Id) 
            select Sc_Id from MSchoolDetails where MSchoolDetails.L_T_Id=@talukid and MSchoolDetails.Y_Id=@yearid
        fetch next from c into @talukid
    END
    select * from  #Sc_Id;---Here you got your final result.
close c
deallocate c
DROP TABLE #Sc_Id
END

Upvotes: 0

Kevin Stricker
Kevin Stricker

Reputation: 17388

I'm inferring from your question that this is the complete solution you're actually looking for:

    SELECT MSchoolDetails.* 
      FROM MLTalukDetails
INNER JOIN MSchoolDetails ON MTalukDetails.l_t_id = MSchoolDetails.L_T_Id
     WHERE MLTalukDetails.L_D_ID = 4 AND MSchoolDetails.Y_Id=@yearid

Normally one has a join condition which involves both tables.

I'd suggest reading up on the various joins.

Upvotes: 1

Related Questions