Click here to Skip to main content
15,922,407 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
hii all.

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

my problem is that i m fatching the data of a single block in all the blocks of a particular distt.. i want to fatch data blockwise.. each disst has diffrnt differnt coulms..
please give me correct syntax.. i want to store data in temp table using loop..suppose no. of block in particular distt is 6.. then how to insert data in temp table block wise


thanks in advance


my stored procedure is

SQL
 USE [SCJ]
GO
/****** Object:  StoredProcedure [dbo].[spAGEwisechldREPORT]    Script Date: 04/07/2014 10:30:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAGEwisechldREPORT] 
	-- Add the parameters for the stored procedure here
	 @DISTID VARCHAR(250)
   -- @BLOCKID VARCHAR(50)
    
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
   
     @vQuery1 AS VARCHAR (max); 
    
    SET @ChildTblName = '[' + @DISTID + 'Child_D' + ']'
    --SET @FamilyTblName = '[' + @DistrictID + 'Family_D' + ']'  
    
    
    
      CREATE TABLE #tblagereoprt
    (
        
        blockname  nvarchar(30),
        stage1   nvarchar(20), 
        stage2   nvarchar(10) ,
        stage3   nvarchar(10), 
        total    nvarchar(10)
     
    );
    
  SET @vQuery1='SELECT blockname, stage1, stage2, stage3,(stage1+stage2+stage3) as total
   FROM  (SELECT blockname as blockname FROM BLOCK where distt='+ @DISTID +' ) BLK
   CROSS JOIN (SELECT COUNT(age) as stage1 FROM ' + @ChildTblName + ' where (age>=6 and age<=10)  ) a
   CROSS JOIN (SELECT COUNT(age) as stage2 FROM ' + @ChildTblName + ' where (age>=11 and age<=14)  ) b
   CROSS JOIN (SELECT COUNT(age) as stage3 FROM ' + @ChildTblName + ' where(age>=6 and age<=14)) c'
  
   
    exec(@vQuery1)
   
    select * from  #tblagereoprt
    
    drop table #tblagereoprt;
  
END

  
END
Posted
Updated 6-Apr-14 20:56pm
v5
Comments
PIEBALDconsult 5-Apr-14 1:23am    
Any reason not to use one query with UNION ALL?
bindash 5-Apr-14 1:24am    
no reason .. but how to combined 3 query into one query

1 solution

Do you want output like this?

SQL
ALTER PROCEDURE [dbo].[spAGEwisechldREPORT]
     @DISTID varchar(250)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ChildTblName AS VARCHAR (125)
 DECLARE @selectQuery AS VARCHAR (max)
   SET @ChildTblName = @DISTID + 'Child_D'

    SET @selectQuery = 'SELECT COUNT(age) AS Count from '+QuoteName(@ChildTblName)+' where (age>=6 and age<=10)'
    SET @selectQuery = @selectQuery + ' UNION ALL  select COUNT(age) AS Count from '+ QuoteName(@ChildTblName)+' where ISDATE(dbo)=1 and (age>=11 and age<=14)'
    SET @selectQuery = @selectQuery + ' UNION ALL SELECT COUNT(age) AS Count from '+QuoteName(@ChildTblName)+' where ISDATE(dbo)=1 and (age>=6 and age<=14)'

    EXEC (@selectQuery)
END
Go
 
Share this answer
 
Comments
bindash 5-Apr-14 2:43am    
yes i want to store this result in 3 diffrnt column in a temp table

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900