Click here to Skip to main content
14,666,459 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 12-Jul-14 0:00am
v2
Comments
Maciej Los 12-Jul-14 6:04am
   
In my opion your SP can be written in a simple way. Please, provide sample data. Use "Improve question" widget.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Please, read my comment to the question.

Try to add brackets, replace:
FROM ' + @ChildTblName + ' WHERE
with
FROM (' + @ChildTblName + ') AS T WHERE
   
Comments
bindash 14-Jul-14 2:59am
   
it's not working.. thnks for reply

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100