SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

create PROCEDURE [dbo].[GetNewsByPage]
	@searchStr varchar(500),
	@pageIndex int,
	@pageSize int,
	@pageCount int output,
	@recordCount int output
AS
	SELECT @pageIndex = @pageIndex - 1
	
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) FROM news WHERE ' + @searchStr
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	SET @pageCount = (@recordCount + @pageSize-1)/@pageSize

	DECLARE @sqlStr varchar(2000),@size int

	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId DESC'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId ASC) TempTable ORDER BY pkId DESC'
	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM news WHERE ' + @searchStr + ' ORDER BY pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END
	EXEC(@sqlStr)
	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].[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(100),
	@subheading varchar(100),
	@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
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
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) 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)
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(100),
	@subheading varchar(100),
	@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
)
AS
	SET NOCOUNT OFF;
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 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].[GetNewsByBigClassIdAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdAndPassed]
GO


CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
	 SET NOCOUNT ON 	
	declare @vSQL varchar(1000)
select @vSQL = '
	select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,d.name "department" 
	from news n,users u,department d 
	where	n.gate<>'+Convert(varchar,@gateId)+' and   
			n.usersId=u.pkId and 
			n.departmentId=d.pkId and 
			bigClassId='+Convert(varchar,@bigClassId)+' and 
			n.gradeId<='+convert(varchar,@gradeId)+' and 
			n.passed='+convert(varchar,@passed)+'  and 
			n.validDays <> 0 and
			n.pkId <> all (
							select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId 
							from news 
							where	gate<>'+Convert(varchar,@gateId)+' and   
									bigClassId='+Convert(varchar,@bigClassId)+' and 
									gradeId<='+convert(varchar,@gradeId)+' and 
									passed='+convert(varchar,@passed)+' and
									validDays <> 0 
							order by istop desc,pkId desc
						   )
	 order by istop desc,n.pkId desc'
Execute (@vSQL)
	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].[GetNewsByAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAll]
GO


CREATE PROCEDURE [dbo].[GetNewsByAll]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@searchStr varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = '  validDays <> 0 '
	SET @searchStr = '%'+@searchStr+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (content like ''' + @searchStr + ''''
	SET @str = @str + ' or author like ''' + @searchStr + ''''
	SET @str = @str + ' or listTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
	SET @str = @str + ' or subheading like ''' + @searchStr + ''''
	SET @str = @str + ') and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int, @recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(PkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' *  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

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].[GetNewsByTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByTitle]
GO


CREATE PROCEDURE [dbo].[GetNewsByTitle]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@title varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' validDays <> 0 '
	SET @title = '%'+@title+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and (listTitle like ''' + @title + ''''
	SET @str = @str + ' or intactTitle like ''' + @title + ''''
	SET @str = @str + ' or subheading like ''' + @title + ''''
	SET @str = @str + ') and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(PkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' *  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

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].[GetNewsByAuthor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAuthor]
GO


CREATE PROCEDURE [dbo].[GetNewsByAuthor]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@author varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' validDays <> 0 '
	SET @author = '%'+@author+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	SET @str = @str + ' and author like ''' + @author + ''''
	SET @str = @str + ' and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(PkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' *  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)
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].[GetNewsByContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByContent]
GO


CREATE PROCEDURE [dbo].[GetNewsByContent]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@content varchar(100),
	@gateId int,
	@bigClassId int
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET  @str = ' validDays <> 0 '
	SET @content = '%'+@content+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
		
	SET @str = @str + ' and content like ''' + @content + ''''
	SET @str = @str + ' and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+STR(@gradeId)
	SET @str = @str + ' and gate<>'+STR(@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, pkId ASC'
	SET @orderTypeD = 'isTop DESC, pkId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int,@recordCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(PkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' *  FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' * FROM '+Convert(nvarchar(50),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

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].[GetNewsBySpecialAndpassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsBySpecialAndpassed]
GO


CREATE PROCEDURE [dbo].[GetNewsBySpecialAndpassed]
@special int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) 
	+ ' n.*,u.userName,d.name "department" from news n,users u,department d where   n.gate<>'
	+Convert(varchar,@gateId)+' and  (specialId='+Convert(varchar,@special)
	+') and n.usersId=u.pkId and (n.isDuplicated=0 or n.isDuplicated is null) and n.departmentId=d.pkId and n.gradeId<='
	+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)
	+' and validDays <> 0 and n.pkId < all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))
	+' pkId from news where     gate<>'+Convert(varchar,@gateId)+' and (specialId='
	+Convert(varchar,@special)+')  and gradeId<='+convert(varchar,@gradeId)+' and isDuplicated<>1 and passed='
	+convert(varchar,@passed)+' and validDays <> 0 order by istop desc,pkId desc)  order by istop desc,n.pkId desc'
Execute (@vSQL)
	
	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].[GetNewsByRecommand]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByRecommand]
GO


CREATE PROCEDURE [dbo].[GetNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL ='select top ' + convert(varchar, @pageSize) + ' pkId,listTitle,intactTitle,updateTime,imageName,isImageNews,width,height,istop 
				from news 
				where   gate<>'+Convert(varchar,@gateId)+' and  IsImageNews=0 and isDuplicated<>1 and 
						gradeId<='+convert(varchar,@gradeId)+' and passed=1  and isRecommand=1 and validDays<> 0
				order by istop desc,pkId desc'
Execute (@vSQL)
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].[GetImageNewsByRecommand]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetImageNewsByRecommand]
GO


CREATE PROCEDURE [dbo].[GetImageNewsByRecommand]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL ='select top 1 * from news where validDays <> 0 and passed=1   and isRecommand=1  and IsImageNews=1   and gate<>'+Convert(varchar,@gateId)+'  and gradeId<='+Convert(varchar,@gradeId)+' order by istop desc,pkId desc'
Execute (@vSQL)
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].[GetLatestNewsByPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLatestNewsByPassed]
GO


CREATE PROCEDURE [dbo].[GetLatestNewsByPassed]
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,	
	@recordCount int ,
	@pageCount int 
AS	
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(*) FROM News WHERE gate<>'+Convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and validDays <> 0  and passed='+convert(varchar,@passed)+' and (isDuplicated=0 or isDuplicated is null) and  isImageNews=0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY n.updateTime DESC, n.pkId DESC'	
	--查询最后一页
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' +STR(@size)  + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and isImageNews=0 ORDER BY n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP '  + STR(@recordCount - @pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' + STR(@pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and (n.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY n.updateTime DESC, n.pkId DESC) TempTable1 ORDER BY updateTime ASC, pkId ASC) TempTable2 ORDER BY updateTime DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

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].[GetNewsByClick]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByClick]
GO


CREATE PROCEDURE [dbo].[GetNewsByClick]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,d.name "department" 
				from news n,users u,department d 
				where   n.gate<>'+Convert(varchar,@gateId)+' and 
						(n.isDuplicated=0 or n.isDuplicated is null) and 
						n.bigClassId not in (select pkId 
											 from bigClass 
											 where templateId in (49,50,53,54)
											 ) and 
						n.usersId=u.pkId and 
						n.departmentId=d.pkId and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.passed=1 and 
						validDays <> 0
				order by n.click desc'
Execute (@vSQL)	
	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].[GetNewsByBigClassIdAndisImageNewsAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdAndisImageNewsAndPassed]
GO


CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndisImageNewsAndPassed]
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' n.*,u.userName,s.name "special",d.name "department" ,g.name "grade" 
				from news n,users u,department d  ,grade g, special s 
				where   n.gate<>'+Convert(varchar,@gateId)+' and  
						n.validDays <> 0 and 
						n.passed=1 and 
						n.bigClassId='+Convert(varchar,@bigClassId)+' and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.departmentId=d.pkId and 
						n.gradeId=g.pkId and 
						n.usersId=u.pkId  and 
						isImageNews=1 and 
						n.specialId = s.pkId and 
						n.pkId <> all ( select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId 
										from news  
										where   gate<>'+Convert(varchar,@gateId)+' and  
												validDays <> 0 and
												passed=1 and 
												bigClassId='+Convert(varchar,@bigClassId)+'  and 
												isImageNews=1 and 
												gradeId<='+convert(varchar,@gradeId)+' 
										order by istop desc,pkId desc
									   )  
				order by istop desc,n.pkId desc'
Execute (@vSQL)	
	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].[InfoValidUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InfoValidUpdate]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-11-15>
-- Description:	<信息有效期更新>
-- =============================================
CREATE PROCEDURE InfoValidUpdate 

AS
BEGIN
	SET NOCOUNT ON;
	UPDATE NEWS SET validDays = validDays - 1 WHERE validDays > 0
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].[GetNewsSearchCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsSearchCount]
GO


CREATE PROCEDURE [dbo].[GetNewsSearchCount]
	@searchStr varchar(100),
	@bigClassId int,
	@type int,
	@gradeId int,
	@gateId int,
	@recordCount int output
AS	
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' validDays <> 0 '
	SET @searchStr = '%'+Convert(varchar,@searchStr)+'%'
	IF (@bigClassId <> 0)
		SET @str = @str + ' and bigClassId='+STR(@bigClassId)
	
	IF (@type=0)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''''
			SET @str = @str + ' or content like ''' + @searchStr + ''''
			SET @str = @str + ' or author like ''' + @searchStr + ''')' 
		END
	IF(@type=1)
		BEGIN
			SET @str = @str + ' and (listTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or intactTitle like ''' + @searchStr + ''''
			SET @str = @str + ' or subheading like ''' + @searchStr + ''')'
		END
	IF(@type=2)
		SET @str = @str + ' and author like ''' + @searchStr + ''''
	IF(@type=3)
		SET @str = @str + ' and content like ''' + @searchStr + ''''

	SET @str = @str + ' and passed=1 and (isDuplicated<>1 or isDuplicated is null) and gradeId<='+Convert(varchar,@gradeId)
	SET @str = @str + ' and gate<>'+Convert(varchar,@gateId)

	--设置查询表名称
	DECLARE @tableName nvarchar(50)
	SET @tableName = 'news'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) from '+Convert(nvarchar(50),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
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].[GetNewsByBigClassIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdCount]
GO


CREATE PROCEDURE [dbo].[GetNewsByBigClassIdCount]
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output,
@isForeGround int
AS
	SET NOCOUNT ON
	declare @num int
	declare @vSQL varchar(1000)
	IF (@isForeGround=0)
	BEGIN
		select @vSQL=
			case @table
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' return'				
				when 'news' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+ ' return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'	--魏兆佳20070727
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'		--魏兆佳20070727
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'			--魏兆佳20070727
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' return'			--魏兆佳20070801
				when 'picNews'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
				when 'picNews1'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406
				when 'picNews2'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406				
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' return'
			end
	END 
	ELSE IF (@isForeGround=1)
	BEGIN
		select @vSQL=
			case @table
				when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
				when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'				
				when 'news' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'gameTitleList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'	--魏兆佳20070727
				when 'gamePicList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070727
				when 'gameList' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070727
				when 'todayInHistory' then 'ALTER procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and bigclassId= '+convert(varchar,@bigclassId)+' and gradeId <= '+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'		--魏兆佳20070801
				when 'picNews'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'
				when 'picNews1'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'--李聪杰20060406
				when 'picNews2'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' and passed=1 and validDays <> 0 return'--李聪杰20060406					
				ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' and passed=1 return'
			end
	END	
	Execute (@vSQL)
	execute GetCount @count=@num output
	select @count=@num
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].[GetNewsByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByPkId]
GO

CREATE PROCEDURE [dbo].[GetNewsByPkId] 
	@pkId int =null, 
	@gradeId int=1, 
	@gateId int 
AS 
	select	n.*,u.userName,s.name 'special' ,d.name 'department' ,g.name 'grade' 
	from	news n, users u, department d, grade g, special s 
	where	@pkId=n.pkId and 
			n.gradeId<=@gradeId and 
			n.gate<>@gateId and 
			n.departmentId=d.pkId and 
			n.gradeId=g.pkId and 
			n.usersId=u.pkId and 
			n.specialId=s.pkId
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].[GetInfoSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetInfoSearch]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-11-23>
-- Description:	<获取具体信息查询栏目对应的表信息>
-- =============================================
CREATE PROCEDURE GetInfoSearch 
	@BigClassID int 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 1 * FROM InfoSearch WHERE BigClassID = @BigClassID
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].[GetInfoList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetInfoList]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-11-23>
-- Description:	<获取具体信息查询栏目对应的表数据>
-- =============================================
CREATE PROCEDURE [dbo].[GetInfoList]
	@TableName varchar(255),
	@PageSize int = 20,
	@PageIndex int = 1,
	@PageCount int output,
	@Recordcount int output
AS	
	SELECT @PageIndex = @PageIndex - 1
	
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) FROM ' + @TableName
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @Recordcount = @tempNum

	SET @PageCount = (@Recordcount + @PageSize-1)/@PageSize

	DECLARE @sqlStr varchar(2000),@size int

	SET @size = @Recordcount%@PageSize	
	IF(@size=0)
		SET @size = @PageSize	

	IF (@PageIndex = 0) OR (@PageIndex > @PageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ' + @TableName + ' ORDER BY pkId ASC'	
	ELSE IF @PageIndex = @PageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM ' + @TableName + ' ORDER BY pkId DESC) TempTable ORDER BY pkId ASC'	
	ELSE IF @PageIndex > @PageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@Recordcount - @PageSize * @PageIndex ) + ' * FROM ' + @TableName + ' ORDER BY pkId DESC) TempTable ORDER BY pkId ASC'
	ELSE
	BEGIN							
		SET @PageIndex=@PageIndex+1
		SET @sqlStr = '	SELECT * FROM (	SELECT TOP ' + STR(@PageSize) + ' * 
										FROM (	SELECT TOP ' + STR(@PageSize * @PageIndex ) + ' * 
												FROM ' + @TableName + ' 
												ORDER BY pkId ASC
											 ) TempTable1 ' + ' 
										ORDER BY pkId DESC
									  ) TempTable2 
						ORDER BY pkId ASC'
	END
	EXEC(@sqlStr)
	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].[UpdateInfoSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateInfoSearch]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-11-27>
-- Description:	<设置具体信息查询栏目对应的表信息>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateInfoSearch]
	@pkId int,
	@MenuID int,
	@BigClassID int,
	@Condition varchar(1500),
	@WhereCondition varchar(1500),
	@FieldList varchar(2000),
	@Result varchar(2000),
	@TableName varchar(255),
	@Memo ntext
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE InfoSearch SET 
		menuId = @MenuID, 
		bigClassId = @BigClassID, 
		condition = @Condition, 
		wherecondition = @WhereCondition, 
		fieldList = @FieldList,
		result = @Result, 
		tableName = @TableName, 
		memo = @Memo
	WHERE pkId = @pkId
END

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].[InsertInfoSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertInfoSearch]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-11-29>
-- Description:	<插入具体信息查询栏目对应的表信息>
-- =============================================
CREATE PROCEDURE [dbo].[InsertInfoSearch] 
	@pkId int output,
	@MenuID int,
	@BigClassID int,
	@Condition varchar(1500),
	@WhereCondition varchar(1500),
	@FieldList varchar(2000),
	@Result varchar(2000),
	@TableName varchar(255),
	@Memo ntext
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		INSERT INTO InfoSearch(menuId, bigClassId, condition, wherecondition, fieldList, result, tableName, memo) 
		VALUES (@MenuID, @BigClassID, @Condition, @WhereCondition, @FieldList, @Result, @TableName, @Memo)
	COMMIT TRANSACTION
	SET @pkId = SCOPE_IDENTITY()
END

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].[GetNewsByPkIdAndClick]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByPkIdAndClick]
GO


CREATE PROCEDURE [dbo].[GetNewsByPkIdAndClick]
@pkId int =null,
@gradeId int=1,
@gateId int 
AS
	 SET NOCOUNT ON 
BEGIN TRANSACTION
	update news
	set click=click+1
	where pkId=@pkId
	select top 1 *
	from news
	where @pkId=pkId
	  and gradeId<=@gradeId
	  and gate<>@gateId
	
	
 COMMIT TRANSACTION
 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].[DeleteInfoSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteInfoSearch]
GO

-- =============================================
-- Author:		<Yinyf>
-- Create date: <2007-12-4>
-- Description:	<删除具体信息查询栏目对应的表信息>
-- =============================================
CREATE PROCEDURE DeleteInfoSearch
	@BigClassID int
AS
BEGIN	
	SET NOCOUNT ON;
	DELETE FROM InfoSearch WHERE bigClassId = @BigClassID
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].[GetRegServiceList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRegServiceList]
GO



create PROCEDURE [dbo].[GetRegServiceList]

AS
	
	SELECT *
	FROM RegServiceList	
	  
	order by pkId  desc
	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].[GetRegServiceListByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRegServiceListByPkId]
GO



create PROCEDURE [dbo].[GetRegServiceListByPkId]
	@pkId int
AS
	
	SELECT *
	FROM RegServiceList	
	where pkId = @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].[InsertRegServiceList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertRegServiceList]
GO




create PROCEDURE [dbo].[InsertRegServiceList]
(
	@pkId int,
	@serviceId int,
	@regServiceCode varchar(250),
	@isAllowService int,
	@regTime datetime,
	@auditTime datetime	
)
AS
	SET NOCOUNT OFF;
INSERT INTO RegServiceList
(
	serviceId, 
	regServiceCode, 
	isAllowService,
	regTime,
	auditTime
) VALUES (
	@serviceId, 
	@regServiceCode, 
	@isAllowService,
	@regTime,
	@auditTime
)



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].[UpdateRegServiceList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateRegServiceList]
GO




create PROCEDURE [dbo].[UpdateRegServiceList]
(
	@pkId int,
	@serviceId int,
	@regServiceCode varchar(250),
	@isAllowService int,
	@regTime datetime,
	@auditTime datetime	
)
AS
	SET NOCOUNT OFF;
Update RegServiceList
set
	serviceId = @serviceId, 
	regServiceCode = @regServiceCode, 
	isAllowService = @isAllowService,
	regTime = @regTime,
	auditTime = @regTime




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].[DeleteRegServiceList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteRegServiceList]
GO




CREATE PROCEDURE [dbo].[DeleteRegServiceList]
	@pkId int
AS
	Delete from ServiceClass where regServiceListId = @pkId
	 DELETE FROM RegServiceList WHERE pkId = @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].[GetMenuByRegServiceId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMenuByRegServiceId]
GO



create PROCEDURE [dbo].[GetMenuByRegServiceId]
@regServiceId int
AS
	/* SET NOCOUNT ON */
	SELECT b.*,
	       b.pkId "bigClassId"
	FROM bigClass b,ServiceClass c
	where b.pkId=c.bigClassId
	  and c.RegServiceListId=@regServiceId
	 
	order by c.pkId  desc
	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].[DeleteServiceClassByRegServiceIdAndBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteServiceClassByRegServiceIdAndBigClassId]
GO



create PROCEDURE [dbo].[DeleteServiceClassByRegServiceIdAndBigClassId]
(
	@regServiceId int,	
	@bigClassId int
)
AS
	SET NOCOUNT OFF;
       
    DELETE FROM ServiceClass WHERE RegServiceListId = @regServiceId and bigClassId = @bigClassId  



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].[GetServiceClassByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetServiceClassByPkId]
GO




create PROCEDURE [dbo].[GetServiceClassByPkId]
	@pkId int
AS
	
	SELECT *
	FROM ServiceClass	
	where pkId = @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].[UpdateServiceClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateServiceClass]
GO




create PROCEDURE [dbo].[UpdateServiceClass]
(
	@pkId int,
	@bigClassId int,
	@regServiceListId int
)
AS
	SET NOCOUNT OFF;
Update ServiceClass
set
	bigClassId = @bigClassId, 
	regServiceListId = @regServiceListId




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].[InsertServiceClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertServiceClass]
GO





create PROCEDURE [dbo].[InsertServiceClass]
(
	@pkId int,
	@bigClassId int,
	@regServiceListId int
)
AS
	SET NOCOUNT OFF;
INSERT INTO ServiceClass
(
	bigClassId, 
	regServiceListId
) VALUES (
	@bigClassId, 
	@regServiceListId
)




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].[GetRegServiceListByRegServiceCodeAndServiceId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRegServiceListByRegServiceCodeAndServiceId]
GO




create PROCEDURE [dbo].[GetRegServiceListByRegServiceCodeAndServiceId]
	@regServiceCode varchar(250),
	@serviceId int
AS
	
	SELECT *
	FROM RegServiceList	
	where regServiceCode = @regServiceCode and serviceId= @serviceId
	
	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].[DynamicTableInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableInsert]
GO

CREATE PROCEDURE dbo.DynamicTableInsert 
	@PkId int OUTPUT,
	@TableName varchar(255),
	@CreateTable varchar(6000)
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		INSERT INTO DynamicTable (tableName, createTable) 
		VALUES (@TableName, @CreateTable)
	COMMIT TRANSACTION
	SET @pkId = SCOPE_IDENTITY()
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].[DynamicTableDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableDelete]
GO

CREATE PROCEDURE dbo.DynamicTableDelete
	@PkId int
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		DELETE FROM DynamicTable WHERE pkId = @PkId
	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].[DynamicTableDeleteByTableName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableDeleteByTableName]
GO

CREATE PROCEDURE dbo.DynamicTableDeleteByTableName
	@TableName varchar(255)
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		DELETE FROM DynamicTable WHERE LTRIM(RTRIM(tableName)) = LTRIM(RTRIM(@TableName))
	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].[DynamicTableUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableUpdate]
GO

CREATE PROCEDURE dbo.DynamicTableUpdate
	@PkId int,
	@TableName varchar(255),
	@CreateTable varchar(6000)
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		 UPDATE DynamicTable SET tableName = @TableName, createTable = @CreateTable WHERE pkId = @PkId
	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].[DynamicTableList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableList]
GO

CREATE PROCEDURE [dbo].[DynamicTableList]
AS
BEGIN
	SET NOCOUNT OFF;
	SELECT * FROM DynamicTable
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].[DynamicTableSearchByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DynamicTableSearchByPkId]
GO

CREATE PROCEDURE dbo.DynamicTableSearchByPkId
	@PkId int
AS
BEGIN
	SET NOCOUNT OFF;
	SELECT TOP 1 * FROM DynamicTable WHERE pkId = @PkId
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].[GetNewsByPkIdForDuplicating]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByPkIdForDuplicating]
GO

CREATE PROCEDURE [dbo].[GetNewsByPkIdForDuplicating] @pkId int =null AS select n.*,u.userName,s.name 'special' ,d.name 'department' ,g.name 'grade' from news n, users u, department d, grade g, special s where @pkId=n.pkId and n.departmentId=d.pkId and n.gradeId=g.pkId and n.usersId=u.pkId and n.specialId=s.pkId
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].[InsertContributePostil]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertContributePostil]
GO


--region [dbo].[InsertContributePostil]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[InsertContributePostil]
-- Date Generated: 2007骞?2鏈?7鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[InsertContributePostil]
	@title varchar(250),
	@content text,
	@isSystem int,
	@pkId int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO [dbo].[ContributePostil] (
	[title],
	[content],
	[isSystem]
) VALUES (
	@title,
	@content,
	@isSystem
)

SET @pkId = SCOPE_IDENTITY()

--endregion


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].[UpdateContributePostil]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateContributePostil]
GO


--region [dbo].[UpdateContributePostil]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[UpdateContributePostil]
-- Date Generated: 2007骞?2鏈?7鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[UpdateContributePostil]
	@pkId int,
	@title varchar(250),
	@content text,
	@isSystem int
AS

SET NOCOUNT ON

UPDATE [dbo].[ContributePostil] SET
	[title] = @title,
	[content] = @content,
	[isSystem] = @isSystem
WHERE
	[pkId] = @pkId

--endregion


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].[DeleteContributePostil]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteContributePostil]
GO


--region [dbo].[DeleteContributePostil]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[DeleteContributePostil]
-- Date Generated: 2007骞?2鏈?7鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[DeleteContributePostil]
	@pkId int
AS

SET NOCOUNT ON

DELETE FROM [dbo].[ContributePostil]
WHERE
	[pkId] = @pkId

--endregion


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].[GetContributePostilByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetContributePostilByPkId]
GO


--region [dbo].[GetContributePostilByPkId]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[GetContributePostilByPkId]
-- Date Generated: 2007骞?2鏈?7鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[GetContributePostilByPkId]
	@pkId int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[ContributePostil]
WHERE
	[pkId] = @pkId

--endregion


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].[GetContributePostil]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetContributePostil]
GO


--region [dbo].[GetContributePostil]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Administrator using CodeSmith 3.2.7.955
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[GetContributePostil]
-- Date Generated: 2007骞?2鏈?7鏃?
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[GetContributePostil]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	*
FROM
	[dbo].[ContributePostil]

--endregion


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].[UserApplyInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserApplyInsert]
GO

CREATE PROCEDURE [dbo].[UserApplyInsert] 
	@PkId int OUTPUT,	
	@UnitName varchar(150),
	@IBE varchar(150),
	@UserName varchar(20),
    @Password varchar(18),
    @Province varchar(150),
	@City varchar(150),
	@County varchar(150),
    @UnitType int,
    @Phone varchar(50),
    @Email varchar(80),
	@AllowSendSms bit,
	@AllowSmsRemind bit,
	@Memo nText
AS
BEGIN
	SET NOCOUNT OFF;
	BEGIN TRANSACTION
		INSERT INTO UserApply (unitName, ibe, userName, password, province, city, county, unitType, phone, email, allowSendSms, allowSmsRemind, memo) 
		VALUES (@UnitName, @IBE, @UserName, @Password, @Province, @City, @County, @UnitType, @Phone, @Email, @AllowSendSms, @AllowSmsRemind, @Memo)
	COMMIT TRANSACTION
	SET @pkId = SCOPE_IDENTITY()
END
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<>'AdminLogin'/*李聪杰20060328*/
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'/*李聪杰20060405*/
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId
		and t.code <> 'userApply'
	  
	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].[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'
		and t.code<>'SearchHorizontal'
		and t.code<>'LoginHorizontal'
		and t.code<>'baidu'
		and t.code<>'shareNews'
		and t.code<>'LatestNews'/*李聪杰20060327*/
		and t.code<>'AdminLogin'/*李聪杰20060328*/
		and t.code<>'contributeStat'
		and t.code<>'LatestMessage'/*李聪杰20060405*/
		and t.code<>'contributeTop'
		and t.code<>'latestContribute'
		and m.bigClassId=b.pkId
		and b.templateId=t.pkId
		and t.code <> 'userApply'
		
	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].[GetNewsLayoutTemplate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsLayoutTemplate]
GO



CREATE PROCEDURE [dbo].[GetNewsLayoutTemplate]
AS
	SET NOCOUNT ON;
SELECT * FROM layoutTemplate WHERE layoutTemplateType = 0 ORDER BY pkId 


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].[GetReviewByNewsIdAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetReviewByNewsIdAndPassed]
GO


CREATE PROCEDURE [dbo].[GetReviewByNewsIdAndPassed]
@newsId int=null,
@gradeId int=1
AS
	 SET NOCOUNT ON 
	select *
	from review
	where newsId=@newsId
	 /*and gradeId<=@gradeId*/
	 and passed=1
	 order by pkId desc
	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].[GetAuditingNewsByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingNewsByUserId]
GO









CREATE   PROCEDURE [dbo].[GetAuditingNewsByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(8000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)
--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #test from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId

	SET @str = ' 1=1 '
	SET @str = @str + ' and n.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and b.templateId = t.pkId'
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + '  and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId ' --and n.MenuId=m.pkId 去掉了 fengyb 2008年-1-9日and n.MenuId=m.pkId 
	SET @str = @str + ' and n.duplicatedPkId=0'

--SET @str = @str + ' and ' + @manageAll
print @str
--SET @str = @str + ' and m.pkId in (1795,1796,1803)'
	--设置查询表名称
	DECLARE @tableName nvarchar(100)

	SET @tableName = '#test z, news n,users u,department d,grade g, template t, bigClass b '
print @tableName
	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'sourceMenuId ASC, newsId ASC'
	SET @orderTypeD = 'sourceMenuId DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(4000)



	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(4000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(4000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' and (n.isDuplicated is null or n.isDuplicated =0 ) and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",z.menuName,z.sourceMenuId, n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(4000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)
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].[GetAuditingMessageBoardByUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAuditingMessageBoardByUserId]
GO



CREATE PROCEDURE [dbo].[GetAuditingMessageBoardByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll varchar(1000),
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(4000)

--用临时表先读取授权的栏目和MenuName，然后在读取授权的新闻，
--这样设计不会对news表中的menuId产生依赖，避免因栏目移动产生错误
select b.pkId bigclassId, m.name menuName,m.pkId sourceMenuId into #MessageBoardTemp from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId=@userId


	SET @str = ' 1=1 '
	SET @str = @str + ' and m.bigclassId = z.bigclassId'
	SET @str = @str + ' and z.bigclassId = b.pkid'
	SET @str = @str + ' and m.checked=0 and m.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and m.gate<>'+STR(@gateId)
	SET @str = @str + ' and m.gradeId=g.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(150)
	SET @tableName = '#MessageBoardTemp z, messageBoard m, grade g , bigClass b '

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'isTop ASC, msgId ASC'
	SET @orderTypeD = 'isTop DESC, msgId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(m.pkId) from '+Convert(nvarchar(150),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId  FROM '+Convert(nvarchar(150),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' z.menuName,z.sourceMenuId, m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' z.menuName,z.sourceMenuId,m.*,g.name "grade", m.pkId as msgId FROM '+Convert(nvarchar(150),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END

	--执行查询
	EXEC(@sqlStr)

RETURN



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

