USE [SCJ] GO /****** Object: StoredProcedure [dbo].[spschlyes/noREPORT] Script Date: 04/09/2014 04:57:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spschlyes/noREPORT] -- Add the parameters for the stored procedure here @DISTID VARCHAR(250), @BLOCKcount int AS BEGIN SET NOCOUNT ON; DECLARE @ChildTblName AS VARCHAR (125), @vQuery1 AS VARCHAR (max) SET @ChildTblName = '[' + @DISTID + 'Child_D' + ']' CREATE TABLE #tblschyes_noreoprt ( blockname nvarchar(30), sch_yes nvarchar(20), sch_no nvarchar(10) , total nvarchar(10) ); DECLARE @N varchar SET @N = 1 WHILE (@N <= @BLOCKcount+1) BEGIN SET @vQuery1='SELECT blockname, sch_yes, sch_no,(sch_yes+sch_no) as total FROM (SELECT blockname as blockname FROM BLOCK where distt='+ @DISTID +'AND block='+@N+' ) BLK CROSS JOIN (SELECT COUNT(*) as sch_yes FROM ' + @ChildTblName + ' where (sch_code<>'' or class<>'') AND block='+@N+' ) a CROSS JOIN (SELECT COUNT(*) as sch_no FROM ' + @ChildTblName + ' where (nereason<>'' or droupout<>'') AND block='+@N+' ) b' INSERT INTO #tblschyes_noreoprt exec(@vQuery1) SET @N = @N + 1 END select * from #tblschyes_noreoprt drop table #tblschyes_noreoprt; END exec [spschlyes/noREPORT] '17',6
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)