How to write two queries in a stored procedure when second query depend on first query output in SQL Server

Question

I want to write a stored procedure in which I have two select queries and second queries has where clause that depends on first query output, as

create procedure getRecord
As
Begin
    select * 
    from tblUser 
    where userName = 'Johan'

    select * 
    from tblDistrict 
    where id between @id1 and @id2
end

Here @id1 and @id2 are the first and last id of resultant table of first query

Answer 1

Try this

create procedure getRecord
As
Begin

 select * from tblDistrict where id IN (select Id from tblUser Where userName = 'Johan')

End

Answer 2

There are a variety of ways to achieve this. If you only need the results from the second query, and the first is merely to filter results for the second, then you can nest the first query within a join e.g.

select * 
from tblDistrict 
inner join (select 
           MAX(id) as maxId,
           MIN(id) as minId
        from tblUser  
        where userName = 'Johan') as tbl1 ON
   tblDistrict.id between tbl1.minId and tbl1.minId

If you need outputs from both queries, you can make use of table variables or temp tables. e.g.

select * 
into #tmpTbl1
from tblUser 
where userName = 'Johan'

declare @minId INT,@maxId INT
select @minId=min(id),@maxId=max(id) from #tmpTbl1

select * from #tmpTbl1
select * from tblDistrict where id between @minId and @maxId

drop table #tmpTbl1

I'm assuming you are using between minId and maxId for a reason and therefore didn't change logic to find an exact id matches for for 'Johan' between tblUser and tblDistrict

The second example can easily be modified to use Table variables instead of temp tables. The performance difference is outside the scope for this question though.