如何在sql server中的单个存储过程中的另一个临时表中使用一个临时表列 [英] how to use one temp table column in another temp table in a single stored procedure in sql server

问题描述

hii all。



i面临我的sql server查询问题..



my问题是我在存储过程中使用了2个临时表..我想在第2个临时表的查询中使用第1个临时表列值..



提前感谢



我的存储过程是

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

错误是：无效列名age1



请帮帮我

the error is:invalid column name age1

please help me

推荐答案

请，阅读我对这个问题的评论。



尝试添加括号，替换：

Please, read my comment to the question.

Try to add brackets, replace:

FROM ' + @ChildTblName + ' WHERE

with

with

FROM (' + @ChildTblName + ') AS T WHERE

这篇关于如何在sql server中的单个存储过程中的另一个临时表中使用一个临时表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！