SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAccredit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAccredit]
GO





CREATE PROCEDURE [dbo].[GetAccredit]
AS
	select pkId,name,parentId,modeId
	from menu 
	where modeId<>4
	union
	SELECT m.pkId,m.name,m.parentId,m.modeId
   	FROM menu m,bigClass b,template t	
	 where t.code<>'newsTop'
		and t.code<>'gameTop' --魏兆佳20070727
		and t.code<>'latestGame' --魏兆佳20070727
		and t.code<>'todayInHistoryShort' --魏兆佳20070801
		and t.code<>'todayInHistoryPic' --魏兆佳20070801
		and t.code<>'searchTodayInHistory' --魏兆佳20070801
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav'
		--and t.code<>'search'    沈建昌20080821
		--and t.code<>'SearchHorizontal'   沈建昌20080821
		and t.code<>'LoginHorizontal'
		and t.code<>'baidu'
		and t.code<>'shareNews'
		and t.code<>'LatestNews'/*李聪杰20060327*/
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin'/*李聪杰20060328*/
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'/*李聪杰20060405*/
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAccreditByAdminId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAccreditByAdminId]
GO




CREATE PROCEDURE [dbo].[GetAccreditByAdminId]
	@adminId int
AS
			select m.pkId,name,parentId, modeId
	from menu m,adminAssign a
	where 
	 a.menuId=0
	 and @adminId=a.adminId
	 and m.pkId=0
	union
		select m.pkId,name,parentId, m.modeId
	from menu m,adminAssign a
	where 
	parentId=0
	and a.adminId=@adminId
	and a.menuId=m.pkId
	
	union
	select pkId,name,parentId, modeId
	from menu 
	where 
	modeId<>4 
	and parentId<>0
	union
	SELECT m.pkId,m.name,m.parentId, m.modeId
   	FROM menu m,bigClass b,template t,adminAssign a	
	 where t.code<>'newsTop'
		and t.code<>'gameTop' --魏兆佳20070727
		and t.code<>'latestGame' --魏兆佳20070727
		and t.code<>'todayInHistoryShort' --魏兆佳20070801
		and t.code<>'todayInHistoryPic' --魏兆佳20070801
		and t.code<>'searchTodayInHistory' --魏兆佳20070801
		and t.code<>'fileDownTop'
		and t.code<>'special'
		and t.code<>'login'
		and t.code<>'nav'
		and t.code<>'search'
		and t.code<>'SearchHorizontal'
		and t.code<>'LoginHorizontal'
	    	and t.code<>'baidu'
	   	and t.code<>'shareNews'
		and t.code<>'LatestNews'/*李聪杰20060327*/
		and t.code<>'LatestImageNews'
		and t.code<>'ArticleStatistics'
		and t.code<>'VisitorsSessions'
		and t.code<>'OnlineStatistics'
		and t.code<>'AdminLogin'/*李聪杰20060328*/
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'/*李聪杰20060405*/
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and t.code<>'newsSummary'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId		
	  
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetArticleStatistics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetArticleStatistics]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetArticleStatistics]
(
	@menuId int ,
	@passed int,
    @gradeid int ,
    @gateid int
)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @sql nvarchar(4000)
	declare @num int
	declare @condition varchar(200)
	declare @passeds nvarchar(50)
	if (@passed = -100)
		begin
			set @passeds = ''
		end
	else
		begin 
			set @passeds = ' AND n.passed = ' + convert(nvarchar,@passed)
		end

	select @num=0 
	select @num= count(pkId) from BigClassAssociation where menuId = @menuId
	if (@num = 0)
		BEGIN			
			
			SET @sql = 'select count(*) from news n,users u,department d,bigClass b 
							where  n.gate = '+Convert(varchar,@gateId)+'  
							and n.usersId=u.pkId 
							and n.departmentId=d.pkId 
							and n.bigClassId=b.pkId	
							and n.gradeId<='+convert(varchar,@gradeId)+' 
							and (n.validDays > 0 OR n.validDays < 0) ' +
							@passeds +
							' and isDuplicated != 1'
			
		END
	else
		BEGIN
			set @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
			
			SET @sql = N'select  count(*)  
						 from news n,users u,department d,bigClass b 
						 where '+@condition+' 
							and  n.gate = '+Convert(varchar,@gateId)+'  
							and n.usersId=u.pkId 
							and n.departmentId=d.pkId 
							and n.bigClassId=b.pkId	
							and n.gradeId<='+convert(varchar,@gradeId)+' 
							and (n.validDays > 0 OR n.validDays < 0) ' +
							@passeds +
							' and isDuplicated != 1 '
			
		End
	--print @sql
	exec (@sql)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertMedianews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMedianews]
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[InsertMedianews] 
	-- Add the parameters for the stored procedure here
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int output,
	@updateIp varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int,
	@validDays int,
    @mediaAutostart int,
	@AuditUserID	int
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	 BEGIN TRANSACTION
IF (@validDays is null)
	set @validDays = -1	
IF (@isDuplicated is null)
	set @isDuplicated = 0
IF (@duplicatedPkId is null)
	set @duplicatedPkId = 0

IF (@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

INSERT INTO news(specialId, gradeId, gate, departmentId, listTitle, intactTitle, subheading, isRecommand, isTop, useListTitle, showReviewLink, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, readPoint, itemType, content, descript, usersId, editor, click, updateTime, passed, isReview, isImageNews, imageName, MenuId, bigClassId, width, height,updateIp,useSubHeading, isDuplicated, duplicateFrom, duplicatedPkId, validDays, mediaAutostart, AuditUserID) VALUES (@specialId, @gradeId, @gate, @departmentId, @listTitle, @intactTitle, @subheading, @isRecommand, @isTop, @useListTitle, @showReviewLink, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @readPoint, @itemType, @content, @descript, @usersId, @editor, @click, @updateTime, @passed, @isReview, @isImageNews, @imageName, @MenuId, @bigClassId, @width, @height,@updateIp,@useSubHeading, @isDuplicated, @duplicateFrom, @duplicatedPkId, @validDays, @mediaAutostart, @AuditUserID)
SET @pkId = @@IDENTITY
COMMIT TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateMediaNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateMediaNews]
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateMediaNews]
	(-- Add the parameters for the stored procedure here
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId	int,
	@validDays int,
    @mediaAutostart int,
	@AuditUserID	int)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF (@validDays is null)
	set @validDays = -1	
	
	IF	(@AuditUserID IS NULL)
		BEGIN
			SET @AuditUserID = 0;
		END
UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType, content = @content, descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId, validDays = @validDays, mediaAutostart=@mediaAutostart, AuditUserID = @AuditUserID WHERE (pkId = @pkId)
UPDATE news SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom='news'			-- 更新news时，同时更新news抄送的信息
UPDATE board SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom = 'news'		-- 更新news时，同时更新board抄送的信息
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNews]
GO








CREATE PROCEDURE [dbo].[InsertNews]
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int output,
	@updateIp varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId int,
	@validDays int,
	@AuditUserID	int
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION

IF (@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

IF (@validDays is null)
	set @validDays = -1	

IF (@isDuplicated is null)
	set @isDuplicated = 0

IF (@duplicatedPkId is null)
	set @duplicatedPkId = 0

INSERT INTO news(
	specialId, 
	gradeId, 
	gate, 
	departmentId, 
	listTitle, 
	intactTitle, 
	subheading, 
	isRecommand, 
	isTop, 
	useListTitle, 
	showReviewLink, 
	Author, 
	AuthorMail, 
	CopyFrom, 
	copyFromLink, 
	about, 
	pageType, 
	autoPageCharNum, 
	readPoint, 
	itemType, 
	content, 
	descript,
	usersId,
	editor, 
	click, 
	updateTime, 
	passed, 
	isReview, 
	isImageNews, 
	imageName, 
	MenuId, 
	bigClassId, 
	width, 
	height,
	updateIp,
	useSubHeading, 
	isDuplicated, 
	duplicateFrom, 
	duplicatedPkId, 
	validDays,
	AuditUserID) 
VALUES (
	@specialId, 
	@gradeId, 
	@gate, 
	@departmentId, 
	@listTitle, 
	@intactTitle, 
	@subheading, 
	@isRecommand, 
	@isTop, 
	@useListTitle, 
	@showReviewLink, 
	@Author, 
	@AuthorMail, 
	@CopyFrom, 
	@copyFromLink, 
	@about, 
	@pageType, 
	@autoPageCharNum, 
	@readPoint, 
	@itemType, 
	@content, 
	@descript, 
	@usersId, 
	@editor, 
	@click, 
	@updateTime, 
	@passed, 
	@isReview, 
	@isImageNews, 
	@imageName, 
	@MenuId, 
	@bigClassId, 
	@width, 
	@height,
	@updateIp,
	@useSubHeading, 
	@isDuplicated, 
	@duplicateFrom, 
	@duplicatedPkId, 
	@validDays,
	@AuditUserID)

SET @pkId = @@IDENTITY

  COMMIT TRANSACTION






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateNews]
GO








CREATE PROCEDURE [dbo].[UpdateNews]
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(500),
	@subheading varchar(500),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int,
	@isDuplicated int=0,
	@duplicateFrom varchar(100),
	@duplicatedPkId	int,
	@validDays int,
	@AuditUserID	int
)
AS
	SET NOCOUNT OFF;
IF	(@AuditUserID IS NULL)
	BEGIN
		SET @AuditUserID = 0;
	END

IF (@validDays is null)
	set @validDays = -1	

UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType, content = @content, descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height, isDuplicated = @isDuplicated, duplicateFrom = @duplicateFrom, duplicatedPkId = @duplicatedPkId, validDays = @validDays, AuditUserID = @AuditUserID WHERE (pkId = @pkId)
UPDATE news SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom='news'			-- 更新news时，同时更新news抄送的信息
UPDATE board SET passed = @passed WHERE duplicatedPkId=@pkId AND duplicateFrom = 'news'		-- 更新news时，同时更新board抄送的信息







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUserNewsStatistics]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUserNewsStatistics]
GO



CREATE PROCEDURE [dbo].[GetUserNewsStatistics]
(
	@PageIndex		int,
	@PageSize		int,
	@BigClassIDs	NVARCHAR(4000),
	@StartTime		datetime,
	@EndTime		datetime,
	@FullName		nvarchar(50), 
	@TotalRecords	int output,
	@TotalNews	int output,
	@TotalPassed	int output
)
AS
BEGIN

	SET @PageIndex = @PageIndex - 1
	IF (@PageIndex < 0)
		BEGIN
			SET @PageIndex = 1
		END


    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT    
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    DECLARE @sql nvarchar(4000)
	DECLARE @where nvarchar(4000)
	DECLARE @orderby nvarchar(100)
	
    
--	SET @where = ' WHERE n.UpdateTime >= ''' + CONVERT(nvarchar, @StartTime, 120) + ''' AND ' +
--				 ' n.UpdateTime <= ''' + CONVERT(nvarchar, @EndTime, 120) + ''' ';

	SET @where = ' WHERE DATEDIFF(day, ''' + CONVERT(nvarchar, @StartTime, 120) + ''', updatetime) >= 0 and 
						 DATEDIFF(day, ''' + CONVERT(nvarchar, @endtime, 120) + ''', updatetime) <= 0 and
						 isDuplicated <> 1 ';

	IF(@BigClassIDs <> '')
		BEGIN
			SET @where = @where + ' AND n.bigclassid IN (' + @BigClassIDs + ')';
		END
	
	IF(@FullName <> '')
		BEGIN
			SET @where = @where + ' AND u.fullname LIKE ''%' + @FullName + '%''';
		END	

    SET @sql = '
	IF object_id(''tempdb..#AllNews'') IS NOT NULL
	BEGIN
		DROP TABLE #AllNews
	END
	
	IF object_id(''tempdb..#PageIndexs'') IS NOT NULL
	BEGIN
		DROP TABLE #PageIndexs
	END

	CREATE TABLE #AllNews
	(    
		NewsID	int,
		UserID	int,
		IsPassed	int
	);

	INSERT	INTO #AllNews (NewsID, IsPassed, UserID)
	SELECT	n.pkId, n.passed, u.pkId
	FROM News as n
		INNER JOIN Users as u ON u.pkId = n.UsersID
	' + 
	@where + '; 

	SELECT @InternalTotalNews = COUNT(*) FROM #AllNews;		
	SELECT @InternalTotalPassed	 = COUNT(*) FROM #AllNews WHERE IsPassed = 1;


	CREATE TABLE #PageIndexs
	(
		IndexId int IDENTITY (0, 1) NOT NULL,
		UserID	int,
		Total	int,
		Passed	int 
	);

	INSERT INTO #PageIndexs (UserID, Total, Passed)
	SELECT UserID, COUNT(*) AS Total, 0 as passed
	FROM #AllNews 	
	GROUP BY UserID
	ORDER BY Passed DESC;

	SELECT @InternalTotalRecords = COUNT(*) FROM #PageIndexs;

	UPDATE #PageIndexs
	SET Passed = 
	(
		SELECT	COUNT(*) 
		FROM	[#AllNews] as a
		WHERE	a.UserID = #PageIndexs.UserID AND
				a.IsPassed = 1
	);
	
	SELECT  u.pkId, u.fullname, p.Total, p.Passed
	FROM	[#PageIndexs] as p
			INNER JOIN [Users] AS u ON u.pkId = p.UserID
	WHERE	p.IndexId >= ' + STR(@PageLowerBound) + ' AND p.IndexId <= ' + STR(@PageUpperBound) + '  ORDER BY  p.Total  DESC , p.Passed DESC
	

	IF object_id(''tempdb..#AllNews'') IS NOT NULL
	BEGIN
		DROP TABLE #AllNews
	END
	
	IF object_id(''tempdb..#PageIndexs'') IS NOT NULL
	BEGIN
		DROP TABLE #PageIndexs
	END'

	--print @sql;
	EXEC sp_ExecuteSQL @sql, 
	N'@InternalTotalRecords int output, @InternalTotalPassed int output, @InternalTotalNews int output', 
	@TotalRecords output ,@TotalPassed	output, @TotalNews output

	RETURN
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

