牛腩新闻发布系统笔记11：存储过程的编写

· 取出最新10条新闻（所属分类、新闻标题、发布时间）

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description:	取出最新10条新闻（所属分类、新闻标题、发布时间）
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectNewNews]
	
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
	order by n.createTime desc
END

· 取出10条热点新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description: 取出10条热点新闻(评论回复最多的新闻)
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectHotNews]

AS
BEGIN
select top 10 n.id,n.title,n.createTime,c.[name], count(com.id) as comCount
    from news n
	inner join category c on n.caId=c.id
    inner join comment com on com.newsId=n.id
	group by  n.id,n.title,n.createTime,c.[name]
	order by n.createTime desc
END

· 根据类别ID取出该类别下的所有新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 20118-27
-- Description:	 根据类别ID取出该类别下的所有新闻
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByCaId] 
	@caid int
AS
BEGIN
	select n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id and n.caId=@caid
	order by n.createTime desc
END

· 根据新闻ID取出该条新闻主体内容

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:根据新闻ID取出该条新闻主体内容
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectById]
	@id int
AS
BEGIN
	select title,[content],createTime,caId from news where id=@id
END

· 根据标题搜索新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:		keithray
-- Create date: 2011-8-27
-- Description:	 根据标题搜索新闻
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByTitle] 
	@title varchar(100)
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
    where n.title like '%'+@title+'%'
	order by n.createTime desc
END

· 根据内容搜索新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	 根据内容搜索新闻
-- =============================================
ALTER PROCEDURE [dbo].[news_SelectByContent]
	
 @content varchar(1000)
AS
BEGIN
	select top 10 n.id,n.title,n.createTime,c.[name] from news n
	inner join category c on n.caId=c.id
    where n.content like '%'+@content+'%'
	order by n.createTime desc
END

· 增加新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	增加新闻
-- =============================================
ALTER PROCEDURE [dbo].[news_Insert]
	@title varchar(100),
	@content text,
	@caId int
AS
BEGIN
		INSERT INTO news (title, [content], caId)
		VALUES(@title,@content,@caId)
END

· 修改新闻

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	修改新闻
-- =============================================
ALTER PROCEDURE [dbo].[news_Update]
	@id int,
	@title varchar(100),
	@content text,
	@caId int
AS
BEGIN
	UPDATE news SET title = @title, [content] = @content, caId = @caid where id=@id
END

· 删除新闻（连同其下新闻评论一起删除）

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		keithray	
-- Create date: 2011-8-27
-- Description:	删除新闻（连同其下新闻评论一起删除）
-- =============================================
ALTER PROCEDURE [dbo].[news_Delete]
	@id int
AS
BEGIN
	--先删除评论
	delete comment where newsId=@id
   --再删除新闻本身
	delete news where id=@id
END