對.text的修改（1）

--尋找最新的圖片
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_GetNewImages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_GetNewImages]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE   Proc blog_GetNewImages
(

 @Count int,
 @IsActive bit
)
as
set rowcount @Count
Select blog_images.Title, blog_images.CategoryID, blog_images.Height, blog_images.Width, blog_config.Application, blog_config.Application+'/'+convert(varchar(8),blog_images.CategoryID)+'/t_'+blog_images.[File] as [File], blog_images.Active, blog_images.ImageID From blog_Images,blog_config
where blog_config.blogid=blog_images.blogid and blog_images.Active <> Case @IsActive When 1 then 0 Else -1 End
order by UploadTime desc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

-----取得專題排行
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blog_GetCategoriesTop]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[blog_GetCategoriesTop]
GO

CREATE Proc blog_GetCategoriesTop
(
 @IsActive bit=null,
 @CategoryType tinyint,
 @ParentID int=null,
 @Count int
)
As
set rowcount @Count
if(@ParentID is not null)
Begin
select  blog_LinkCategories.CategoryID, blog_config.Application,blog_LinkCategories.blogID,blog_LinkCategories.Title, blog_LinkCategories.Active,
blog_LinkCategories.CategoryType, blog_LinkCategories.[Description],ParentID ,t.blogcount
FROM blog_LinkCategories,blog_config,(select count(*) as blogcount ,categoryid from blog_links group by categoryid) t
where blog_config.blogid = blog_LinkCategories.blogID and blog_LinkCategories.ParentID=@ParentID and blog_LinkCategories.CategoryType = @CategoryType
and blog_LinkCategories.Active <> Case @IsActive When 1 then 0 Else -1 End and t.categoryid=blog_LinkCategories.CategoryID
ORDER BY blogcount desc;
End
Else
Begin
SELECT blog_LinkCategories.CategoryID,blog_config.Application,blog_LinkCategories.blogID, blog_LinkCategories.Title, blog_LinkCategories.Active,
blog_LinkCategories.CategoryType, blog_LinkCategories.[Description],ParentID,t.blogcount
FROM blog_LinkCategories ,blog_config,(select count(*) as blogcount ,categoryid from blog_links group by categoryid) t
where  blog_config.blogid = blog_LinkCategories.blogID and blog_LinkCategories.CategoryType = @CategoryType
and blog_LinkCategories.Active =@IsActive and t.categoryid=blog_LinkCategories.CategoryID
ORDER BY blogcount desc
End

GO