how to use one temp table column in another temp table in a single stored procedure in sql server

See more:

SQL

server

hii all.

i am facing a problem with my sql server query..

my problem is that i am using 2 temp table in stored procedure.. i want to use 1st temp table column value in query for 2nd temp table ..

thanks in advance

my stored procedure is

USE [ctsfull]
GO
/****** Object:  StoredProcedure [dbo].[spAGEwisechldREPORT]    Script Date: 07/12/2014 14:56:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAGEwisechldREPORT] 
	-- Add the parameters for the stored procedure here
	 @DISTID VARCHAR(2),
     @BLOCKcount int
    
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
   
     @vQuery1 AS VARCHAR (max), 
     @vQuery2 AS VARCHAR (max); 
    
    SET @ChildTblName = '[' + @DISTID + 'Child_D' + ']'
 
 
     SET DATEFORMAT DMY
    
      CREATE TABLE #age_dynamic
    (
      age1 int
    );
    
    BEGIN
    
   SET @vQuery2='SELECT  age1=(DATEDIFF(YEAR,CAST(DOB AS DATETIME),GETDATE())-
                (CASE WHEN DATEADD(MM,DATEDIFF(MONTH,CAST(DOB AS DATETIME),GETDATE()),CAST(DOB AS DATETIME))
                >GETDATE() THEN 0
             ELSE 1 END)) FROM ' + @ChildTblName + ' WHERE ISDATE(DOB)=1'
   
   
   insert into #age_dynamic
   exec(@vQuery2)
   
    END
    
 -- select * from  #age_dynamic
 
      CREATE TABLE #tblagereoprt
    (
        
        blockname  nvarchar(20),
        stage1   nvarchar(20), 
        stage2   nvarchar(10) ,
        stage3   nvarchar(10), 
        total    nvarchar(10)
     
    );
     DECLARE @N varchar
    SET @N = 1
    
     
    WHILE (@N <= @BLOCKcount+1)
    
    BEGIN
     
 
  
   
   SET @vQuery1='SELECT blockname, stage1, stage2, stage3, total
   FROM  (SELECT block, blockname as blockname FROM BLOCK where distt='+ @DISTID +' AND block='+@N+' ) BLK
   
   CROSS JOIN (SELECT COUNT(*) as stage1 FROM ' + @ChildTblName + ' where (age1>=6 and age1<=10) AND block='+@N+' ) a
   CROSS JOIN (SELECT COUNT(*) as stage2 FROM ' + @ChildTblName + ' where (age1>=11 and age1<=14) AND block='+@N+' ) b
   CROSS JOIN (SELECT COUNT(*) as stage3 FROM ' + @ChildTblName + ' where(age1>=6 and age1<=14)AND block='+@N+') c
   CROSS JOIN (SELECT COUNT(*) as total FROM ' + @ChildTblName + ' where block='+@N+') d'
   
 
   insert into #tblagereoprt
   exec(@vQuery1)
   
     
    SET @N = @N + 1
    END
    
  select * from  #tblagereoprt
     
    drop table #tblagereoprt;
  
END

the error is:invalid column name age1

please help me