how to fatch data in temp table blockwise

Question

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 4-Apr-14 19:09pm

bindash

Updated 6-Apr-14 20:56pm

v5

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Schatak · Accepted Answer · 2014-04-04T20:27:00

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