自动生成 删除，更新，搜索，查询，列表，分页等等代码
以下为代码示例
-- Drop the dbo.News_Qst_Category_Count procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_Count')
and type = 'P')
drop procedure News_Qst_Category_Count
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 统计表Qst_Category中的数据量
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[News_Qst_Category_Count]
AS
BEGIN
SELECT COUNT(*) FROM Qst_Category --分类信息表
END
GO
-- Drop the dbo.News_Qst_Category_List procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_List')
and type = 'P')
drop procedure News_Qst_Category_List
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中返回全部列表
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_List]
AS
Begin
SET NOCOUNT ON
SELECT
[CategoryID], --
[Name], --分类名
[ParentID], --父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], --Index
[ListType], --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], --分类链接
[MorePageUrl], --
[AdList], --列表广告位ID
[AdArticle], --内容广告位
[AdPosition1], --广告位1
[AdPosition2], --广告位2
[AdPosition3], --广告位3
[AdPosition4] --广告位4
FROM
dbo.[Qst_Category] --分类信息表
ORDER BY CategoryID DESC
End
GO
-- Drop the dbo.News_Qst_Category_List_Pager procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_List_Pager')
and type = 'P')
drop procedure News_Qst_Category_List_Pager
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照返回对象列表分页
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[News_Qst_Category_List_Pager]
(
@PageIndex int =1,
@PageSize int =10,
@DoCount bit = 0
)
AS
begin
IF(@docount=1)
begin
---统计
Select count(*) from dbo.[Qst_Category]
end
else
begin
if (@PageIndex<=1)
begin
select top (@PageSize) * from [Qst_Category] order by CategoryID desc
end
else
begin
--分页
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * (@PageIndex-1)
SET @PageUpperBound = @PageLowerBound + @PageSize
Select * from (
Select TOP (@PageUpperBound)
ROW_NUMBER() OVER (ORDER BY CategoryID DESC) AS RowIndex,
[CategoryID], --
[Name], --分类名
[ParentID], --父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], --Index
[ListType], --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], --分类链接
[MorePageUrl], --
[AdList], --列表广告位ID
[AdArticle], --内容广告位
[AdPosition1], --广告位1
[AdPosition2], --广告位2
[AdPosition3], --广告位3
[AdPosition4] --广告位4
from dbo.[Qst_Category]
) as Pager
where
Pager.RowIndex > @PageLowerBound
and Pager.RowIndex <= @PageUpperBound
end
end
end
GO
-- Drop the dbo.News_Qst_Category_GetListByParentID procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_GetListByParentID')
and type = 'P')
drop procedure News_Qst_Category_GetListByParentID
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照ParentID返回全部列表
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_GetListByParentID]
(
@ParentID int = 0 --父分类ID [GetList]
)
AS
Begin
SET NOCOUNT ON
SELECT
[CategoryID], --
[Name], --分类名
[ParentID], --父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], --Index
[ListType], --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], --分类链接
[MorePageUrl], --
[AdList], --列表广告位ID
[AdArticle], --内容广告位
[AdPosition1], --广告位1
[AdPosition2], --广告位2
[AdPosition3], --广告位3
[AdPosition4] --广告位4
FROM
dbo.[Qst_Category] --分类信息表
where ParentID = @ParentID
ORDER BY CategoryID DESC
End
GO
-- Drop the dbo.News_Qst_Category_GetListByParentIDPager procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_GetListByParentIDPager')
and type = 'P')
drop procedure News_Qst_Category_GetListByParentIDPager
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照ParentID返回对象列表分页
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[News_Qst_Category_GetListByParentIDPager]
(
@ParentID int = 0 , --父分类ID [GetList]
@PageIndex int =1,
@PageSize int =10,
@DoCount bit = 0
)
AS
begin
IF(@docount=1)
begin
---统计
Select count(*) from dbo.[Qst_Category]
where ParentID = @ParentID
end
else
begin
if (@PageIndex<=1)
begin
select top (@PageSize) * from [Qst_Category] where ParentID = @ParentID
ORDER BY CategoryID DESC
end
else
begin
--分页
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * (@PageIndex-1)
SET @PageUpperBound = @PageLowerBound + @PageSize
Select * from (
Select TOP (@PageUpperBound)
ROW_NUMBER() OVER ( ORDER BY CategoryID DESC
) AS RowIndex,
[CategoryID], --
[Name], --分类名
[ParentID], --父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], --Index
[ListType], --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], --分类链接
[MorePageUrl], --
[AdList], --列表广告位ID
[AdArticle], --内容广告位
[AdPosition1], --广告位1
[AdPosition2], --广告位2
[AdPosition3], --广告位3
[AdPosition4] --广告位4
from dbo.[Qst_Category]
where ParentID = @ParentID
) as Pager
where
Pager.RowIndex > @PageLowerBound
and Pager.RowIndex <= @PageUpperBound
end
end
end
GO
-- Drop the dbo.News_Qst_Category_GetByCategoryID procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_GetByCategoryID')
and type = 'P')
drop procedure News_Qst_Category_GetByCategoryID
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照主键CategoryID返回对象
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[News_Qst_Category_GetByCategoryID]
(
@CategoryID int --
)
AS
SET NOCOUNT ON
SELECT
[CategoryID], --
[Name], --分类名
[ParentID], --父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], --Index
[ListType], --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], --分类链接
[MorePageUrl], --
[AdList], --列表广告位ID
[AdArticle], --内容广告位
[AdPosition1], --广告位1
[AdPosition2], --广告位2
[AdPosition3], --广告位3
[AdPosition4] --广告位4
FROM
dbo.[Qst_Category] --分类信息表
WHERE
[CategoryID] = @CategoryID
GO
-- Drop the dbo.News_Qst_Category_Insert procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_Insert')
and type = 'P')
drop procedure News_Qst_Category_Insert
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 向表 Qst_Category 中插入数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_Insert]
(
@CategoryID int output, --
@Name nvarchar(50), --分类名
@ParentID int = 0 , --父分类ID [GetList]
@SortID int = 0 , --
@Intro ntext = null , --
@MetaKeyword nvarchar(100) = null , --
@MetaDescription nvarchar(100) = null , --
@FolderName nvarchar(50) = null , --
@IndexPageName nvarchar(50) = null , --Index
@ListType int = 0 , --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar(200) = null , --分类链接
@MorePageUrl nvarchar(200) = null , --
@AdList int = 0 , --列表广告位ID
@AdArticle int = 0 , --内容广告位
@AdPosition1 int = 0 , --广告位1
@AdPosition2 int = 0 , --广告位2
@AdPosition3 int = 0 , --广告位3
@AdPosition4 int = 0 --广告位4
)
AS
SET NOCOUNT ON
INSERT INTO dbo.[Qst_Category]
(
[Name], -- 分类名
[ParentID], -- 父分类ID [GetList]
[SortID], --
[Intro], --
[MetaKeyword], --
[MetaDescription], --
[FolderName], --
[IndexPageName], -- Index
[ListType], -- 列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl], -- 分类链接
[MorePageUrl], --
[AdList], -- 列表广告位ID
[AdArticle], -- 内容广告位
[AdPosition1], -- 广告位1
[AdPosition2], -- 广告位2
[AdPosition3], -- 广告位3
[AdPosition4] -- 广告位4
)
VALUES
(
@Name, -- 分类名
@ParentID, -- 父分类ID [GetList]
@SortID, --
@Intro, --
@MetaKeyword, --
@MetaDescription, --
@FolderName, --
@IndexPageName, -- Index
@ListType, -- 列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl, -- 分类链接
@MorePageUrl, --
@AdList, -- 列表广告位ID
@AdArticle, -- 内容广告位
@AdPosition1, -- 广告位1
@AdPosition2, -- 广告位2
@AdPosition3, -- 广告位3
@AdPosition4 -- 广告位4
)
SET @CategoryID = SCOPE_IDENTITY()
GO
-- Drop the dbo.News_Qst_Category_UpdateByCategoryID procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_UpdateByCategoryID')
and type = 'P')
drop procedure News_Qst_Category_UpdateByCategoryID
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 按照 CategoryID 更新表 Qst_Category 中的数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_UpdateByCategoryID]
(
@CategoryID int, --
@Name nvarchar(50), --分类名
@ParentID int = 0 , --父分类ID [GetList]
@SortID int = 0 , --
@Intro ntext = null , --
@MetaKeyword nvarchar(100) = null , --
@MetaDescription nvarchar(100) = null , --
@FolderName nvarchar(50) = null , --
@IndexPageName nvarchar(50) = null , --Index
@ListType int = 0 , --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar(200) = null , --分类链接
@MorePageUrl nvarchar(200) = null , --
@AdList int = 0 , --列表广告位ID
@AdArticle int = 0 , --内容广告位
@AdPosition1 int = 0 , --广告位1
@AdPosition2 int = 0 , --广告位2
@AdPosition3 int = 0 , --广告位3
@AdPosition4 int = 0 --广告位4
)
AS
SET NOCOUNT ON
UPDATE
Qst_Category --分类信息表
SET
[Name] = @Name, --分类名
[ParentID] = @ParentID, --父分类ID [GetList]
[SortID] = @SortID, --
[Intro] = @Intro, --
[MetaKeyword] = @MetaKeyword, --
[MetaDescription] = @MetaDescription, --
[FolderName] = @FolderName, --
[IndexPageName] = @IndexPageName, --Index
[ListType] = @ListType, --列表类型 0为标题列表 1为摘要列表 2为全文列表
[LinkUrl] = @LinkUrl, --分类链接
[MorePageUrl] = @MorePageUrl, --
[AdList] = @AdList, --列表广告位ID
[AdArticle] = @AdArticle, --内容广告位
[AdPosition1] = @AdPosition1, --广告位1
[AdPosition2] = @AdPosition2, --广告位2
[AdPosition3] = @AdPosition3, --广告位3
[AdPosition4] = @AdPosition4 --广告位4
WHERE
[CategoryID] = @CategoryID
GO
-- Drop the dbo.News_Qst_Category_UpdateColumnByCategoryID procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_UpdateColumnByCategoryID')
and type = 'P')
drop procedure News_Qst_Category_UpdateColumnByCategoryID
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 按照 CategoryID 更新表 Qst_Category 中单个字段的数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_UpdateColumnByCategoryID]
(
@IsMutileUpdate bit = 0,--是否为多列更新0为否为是
@CategoryID int = null , --
@Name nvarchar(50) = null , --分类名
@ParentID int = null , --父分类ID [GetList]
@SortID int = null , --
@Intro ntext = null , --
@MetaKeyword nvarchar(100) = null , --
@MetaDescription nvarchar(100) = null , --
@FolderName nvarchar(50) = null , --
@IndexPageName nvarchar(50) = null , --Index
@ListType int = null , --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar(200) = null , --分类链接
@MorePageUrl nvarchar(200) = null , --
@AdList int = null , --列表广告位ID
@AdArticle int = null , --内容广告位
@AdPosition1 int = null , --广告位1
@AdPosition2 int = null , --广告位2
@AdPosition3 int = null , --广告位3
@AdPosition4 int = null --广告位4
)
AS
SET NOCOUNT ON
if @CategoryID is null
return
if @Name is not null
begin
UPDATE dbo.[Qst_Category] SET [Name] = @Name where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @ParentID is not null
begin
UPDATE dbo.[Qst_Category] SET [ParentID] = @ParentID where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @SortID is not null
begin
UPDATE dbo.[Qst_Category] SET [SortID] = @SortID where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @Intro is not null
begin
UPDATE dbo.[Qst_Category] SET [Intro] = @Intro where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @MetaKeyword is not null
begin
UPDATE dbo.[Qst_Category] SET [MetaKeyword] = @MetaKeyword where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @MetaDescription is not null
begin
UPDATE dbo.[Qst_Category] SET [MetaDescription] = @MetaDescription where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @FolderName is not null
begin
UPDATE dbo.[Qst_Category] SET [FolderName] = @FolderName where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @IndexPageName is not null
begin
UPDATE dbo.[Qst_Category] SET [IndexPageName] = @IndexPageName where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @ListType is not null
begin
UPDATE dbo.[Qst_Category] SET [ListType] = @ListType where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @LinkUrl is not null
begin
UPDATE dbo.[Qst_Category] SET [LinkUrl] = @LinkUrl where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @MorePageUrl is not null
begin
UPDATE dbo.[Qst_Category] SET [MorePageUrl] = @MorePageUrl where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdList is not null
begin
UPDATE dbo.[Qst_Category] SET [AdList] = @AdList where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdArticle is not null
begin
UPDATE dbo.[Qst_Category] SET [AdArticle] = @AdArticle where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdPosition1 is not null
begin
UPDATE dbo.[Qst_Category] SET [AdPosition1] = @AdPosition1 where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdPosition2 is not null
begin
UPDATE dbo.[Qst_Category] SET [AdPosition2] = @AdPosition2 where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdPosition3 is not null
begin
UPDATE dbo.[Qst_Category] SET [AdPosition3] = @AdPosition3 where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
if @AdPosition4 is not null
begin
UPDATE dbo.[Qst_Category] SET [AdPosition4] = @AdPosition4 where [CategoryID] = @CategoryID
if (@IsMutileUpdate = 0)
begin
return
end
end
GO
-- Drop the dbo.News_Qst_Category_DeleteByCategoryID procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_DeleteByCategoryID')
and type = 'P')
drop procedure News_Qst_Category_DeleteByCategoryID
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照主键CategoryID删除数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_DeleteByCategoryID]
(
@CategoryID int --
)
AS
SET NOCOUNT ON
DELETE FROM [Qst_Category] --分类信息表
where
[CategoryID] = @CategoryID
GO
-- Drop the dbo.News_Qst_Category_DeleteAll procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_DeleteAll')
and type = 'P')
drop procedure News_Qst_Category_DeleteAll
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中删除全部数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_DeleteAll]
AS
SET NOCOUNT ON
Delete from dbo.[Qst_Category]
GO
-- Drop the dbo.News_Qst_Category_DeleteByUserDiyField procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_DeleteByUserDiyField')
and type = 'P')
drop procedure News_Qst_Category_DeleteByUserDiyField
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中按照自定义字段删除数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_DeleteByUserDiyField]
(
@IsMutileDelete bit = 0,--是否为多列删除0为否为是
@CategoryID int = null , --
@Name nvarchar(50) = null , --分类名
@ParentID int = null , --父分类ID [GetList]
@SortID int = null , --
@Intro ntext = null , --
@MetaKeyword nvarchar(100) = null , --
@MetaDescription nvarchar(100) = null , --
@FolderName nvarchar(50) = null , --
@IndexPageName nvarchar(50) = null , --Index
@ListType int = null , --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar(200) = null , --分类链接
@MorePageUrl nvarchar(200) = null , --
@AdList int = null , --列表广告位ID
@AdArticle int = null , --内容广告位
@AdPosition1 int = null , --广告位1
@AdPosition2 int = null , --广告位2
@AdPosition3 int = null , --广告位3
@AdPosition4 int = null --广告位4
)
AS
SET NOCOUNT ON
if @CategoryID is not null
begin
Delete from dbo.[Qst_Category] where [CategoryID] = @CategoryID
if (@IsMutileDelete = 0)
begin
return
end
end
if @Name is not null
begin
Delete from dbo.[Qst_Category] where [Name] = @Name
if (@IsMutileDelete = 0)
begin
return
end
end
if @ParentID is not null
begin
Delete from dbo.[Qst_Category] where [ParentID] = @ParentID
if (@IsMutileDelete = 0)
begin
return
end
end
if @SortID is not null
begin
Delete from dbo.[Qst_Category] where [SortID] = @SortID
if (@IsMutileDelete = 0)
begin
return
end
end
if @MetaKeyword is not null
begin
Delete from dbo.[Qst_Category] where [MetaKeyword] = @MetaKeyword
if (@IsMutileDelete = 0)
begin
return
end
end
if @MetaDescription is not null
begin
Delete from dbo.[Qst_Category] where [MetaDescription] = @MetaDescription
if (@IsMutileDelete = 0)
begin
return
end
end
if @FolderName is not null
begin
Delete from dbo.[Qst_Category] where [FolderName] = @FolderName
if (@IsMutileDelete = 0)
begin
return
end
end
if @IndexPageName is not null
begin
Delete from dbo.[Qst_Category] where [IndexPageName] = @IndexPageName
if (@IsMutileDelete = 0)
begin
return
end
end
if @ListType is not null
begin
Delete from dbo.[Qst_Category] where [ListType] = @ListType
if (@IsMutileDelete = 0)
begin
return
end
end
if @LinkUrl is not null
begin
Delete from dbo.[Qst_Category] where [LinkUrl] = @LinkUrl
if (@IsMutileDelete = 0)
begin
return
end
end
if @MorePageUrl is not null
begin
Delete from dbo.[Qst_Category] where [MorePageUrl] = @MorePageUrl
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdList is not null
begin
Delete from dbo.[Qst_Category] where [AdList] = @AdList
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdArticle is not null
begin
Delete from dbo.[Qst_Category] where [AdArticle] = @AdArticle
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdPosition1 is not null
begin
Delete from dbo.[Qst_Category] where [AdPosition1] = @AdPosition1
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdPosition2 is not null
begin
Delete from dbo.[Qst_Category] where [AdPosition2] = @AdPosition2
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdPosition3 is not null
begin
Delete from dbo.[Qst_Category] where [AdPosition3] = @AdPosition3
if (@IsMutileDelete = 0)
begin
return
end
end
if @AdPosition4 is not null
begin
Delete from dbo.[Qst_Category] where [AdPosition4] = @AdPosition4
if (@IsMutileDelete = 0)
begin
return
end
end
GO
-- Drop the dbo.News_Qst_Category_Find procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_Find')
and type = 'P')
drop procedure News_Qst_Category_Find
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中搜索数据
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_Find]
(
@TopNum int = 0,---如果是则返回全部记录
@SearchUsingOR bit = 0 ,
@CategoryID int = null, --
@Name nvarchar = null, --分类名
@ParentID int = null, --父分类ID [GetList]
@SortID int = null, --
@Intro ntext = null, --
@MetaKeyword nvarchar = null, --
@MetaDescription nvarchar = null, --
@FolderName nvarchar = null, --
@IndexPageName nvarchar = null, --Index
@ListType int = null, --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar = null, --分类链接
@MorePageUrl nvarchar = null, --
@AdList int = null, --列表广告位ID
@AdArticle int = null, --内容广告位
@AdPosition1 int = null, --广告位1
@AdPosition2 int = null, --广告位2
@AdPosition3 int = null, --广告位3
@AdPosition4 int = null --广告位4
)
AS
SET NOCOUNT ON
IF @TopNum <= 0
BEGIN
IF @SearchUsingOR = 0
BEGIN
SELECT * FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is null OR [CategoryID] = @CategoryID ) --
AND (@Name is null OR [Name] = @Name ) --分类名
AND (@ParentID is null OR [ParentID] = @ParentID ) --父分类ID [GetList]
AND (@SortID is null OR [SortID] = @SortID ) --
AND (@MetaKeyword is null OR [MetaKeyword] = @MetaKeyword ) --
AND (@MetaDescription is null OR [MetaDescription] = @MetaDescription ) --
AND (@FolderName is null OR [FolderName] = @FolderName ) --
AND (@IndexPageName is null OR [IndexPageName] = @IndexPageName ) --Index
AND (@ListType is null OR [ListType] = @ListType ) --列表类型 0为标题列表 1为摘要列表 2为全文列表
AND (@LinkUrl is null OR [LinkUrl] = @LinkUrl ) --分类链接
AND (@MorePageUrl is null OR [MorePageUrl] = @MorePageUrl ) --
AND (@AdList is null OR [AdList] = @AdList ) --列表广告位ID
AND (@AdArticle is null OR [AdArticle] = @AdArticle ) --内容广告位
AND (@AdPosition1 is null OR [AdPosition1] = @AdPosition1 ) --广告位1
AND (@AdPosition2 is null OR [AdPosition2] = @AdPosition2 ) --广告位2
AND (@AdPosition3 is null OR [AdPosition3] = @AdPosition3 ) --广告位3
AND (@AdPosition4 is null OR [AdPosition4] = @AdPosition4 ) --广告位4
ORDER BY CategoryID DESC
END
ELSE
BEGIN
SELECT * FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is not null And [CategoryID] = @CategoryID) --
OR (@Name is not null And [Name] = @Name) --分类名
OR (@ParentID is not null And [ParentID] = @ParentID) --父分类ID [GetList]
OR (@SortID is not null And [SortID] = @SortID) --
OR (@MetaKeyword is not null And [MetaKeyword] = @MetaKeyword) --
OR (@MetaDescription is not null And [MetaDescription] = @MetaDescription) --
OR (@FolderName is not null And [FolderName] = @FolderName) --
OR (@IndexPageName is not null And [IndexPageName] = @IndexPageName) --Index
OR (@ListType is not null And [ListType] = @ListType) --列表类型 0为标题列表 1为摘要列表 2为全文列表
OR (@LinkUrl is not null And [LinkUrl] = @LinkUrl) --分类链接
OR (@MorePageUrl is not null And [MorePageUrl] = @MorePageUrl) --
OR (@AdList is not null And [AdList] = @AdList) --列表广告位ID
OR (@AdArticle is not null And [AdArticle] = @AdArticle) --内容广告位
OR (@AdPosition1 is not null And [AdPosition1] = @AdPosition1) --广告位1
OR (@AdPosition2 is not null And [AdPosition2] = @AdPosition2) --广告位2
OR (@AdPosition3 is not null And [AdPosition3] = @AdPosition3) --广告位3
OR (@AdPosition4 is not null And [AdPosition4] = @AdPosition4) --广告位4
ORDER BY CategoryID DESC
END
End
ELSE
BEGIN
IF @SearchUsingOR = 0
BEGIN
SELECT TOP (@TopNum) * FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is null OR [CategoryID] = @CategoryID ) --
AND (@Name is null OR [Name] = @Name ) --分类名
AND (@ParentID is null OR [ParentID] = @ParentID ) --父分类ID [GetList]
AND (@SortID is null OR [SortID] = @SortID ) --
AND (@MetaKeyword is null OR [MetaKeyword] = @MetaKeyword ) --
AND (@MetaDescription is null OR [MetaDescription] = @MetaDescription ) --
AND (@FolderName is null OR [FolderName] = @FolderName ) --
AND (@IndexPageName is null OR [IndexPageName] = @IndexPageName ) --Index
AND (@ListType is null OR [ListType] = @ListType ) --列表类型 0为标题列表 1为摘要列表 2为全文列表
AND (@LinkUrl is null OR [LinkUrl] = @LinkUrl ) --分类链接
AND (@MorePageUrl is null OR [MorePageUrl] = @MorePageUrl ) --
AND (@AdList is null OR [AdList] = @AdList ) --列表广告位ID
AND (@AdArticle is null OR [AdArticle] = @AdArticle ) --内容广告位
AND (@AdPosition1 is null OR [AdPosition1] = @AdPosition1 ) --广告位1
AND (@AdPosition2 is null OR [AdPosition2] = @AdPosition2 ) --广告位2
AND (@AdPosition3 is null OR [AdPosition3] = @AdPosition3 ) --广告位3
AND (@AdPosition4 is null OR [AdPosition4] = @AdPosition4 ) --广告位4
ORDER BY CategoryID DESC
END
ELSE
BEGIN
SELECT TOP (@TopNum) * FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is not null And [CategoryID] = @CategoryID) --
OR (@Name is not null And [Name] = @Name) --分类名
OR (@ParentID is not null And [ParentID] = @ParentID) --父分类ID [GetList]
OR (@SortID is not null And [SortID] = @SortID) --
OR (@MetaKeyword is not null And [MetaKeyword] = @MetaKeyword) --
OR (@MetaDescription is not null And [MetaDescription] = @MetaDescription) --
OR (@FolderName is not null And [FolderName] = @FolderName) --
OR (@IndexPageName is not null And [IndexPageName] = @IndexPageName) --Index
OR (@ListType is not null And [ListType] = @ListType) --列表类型 0为标题列表 1为摘要列表 2为全文列表
OR (@LinkUrl is not null And [LinkUrl] = @LinkUrl) --分类链接
OR (@MorePageUrl is not null And [MorePageUrl] = @MorePageUrl) --
OR (@AdList is not null And [AdList] = @AdList) --列表广告位ID
OR (@AdArticle is not null And [AdArticle] = @AdArticle) --内容广告位
OR (@AdPosition1 is not null And [AdPosition1] = @AdPosition1) --广告位1
OR (@AdPosition2 is not null And [AdPosition2] = @AdPosition2) --广告位2
OR (@AdPosition3 is not null And [AdPosition3] = @AdPosition3) --广告位3
OR (@AdPosition4 is not null And [AdPosition4] = @AdPosition4) --广告位4
ORDER BY CategoryID DESC
END
END
GO
-- Drop the dbo.News_Qst_Category_Find_Pager procedure
if exists (select 1
from sysobjects
where id = object_id('News_Qst_Category_Find_Pager')
and type = 'P')
drop procedure News_Qst_Category_Find_Pager
go
GO
/*
----------------------------------------------------------------------------------------------------
-- Date Created: 2008年5月23日 13:08
-- Created By:CrazyCoder 疯狂代码
-- Purpose: 从表Qst_Category中搜索数据并且分页
-- 表说明 分类信息表
----------------------------------------------------------------------------------------------------
*/
Create PROCEDURE [dbo].[News_Qst_Category_Find_Pager]
(
@DoCount bit = 0,--是否统计记录
@PageIndex int = 1,--当前页码
@PageSize int = 10,--页面大小
@SearchUsingOR bit = 0 ,
@CategoryID int = null, --
@Name nvarchar = null, --分类名
@ParentID int = null, --父分类ID [GetList]
@SortID int = null, --
@Intro ntext = null, --
@MetaKeyword nvarchar = null, --
@MetaDescription nvarchar = null, --
@FolderName nvarchar = null, --
@IndexPageName nvarchar = null, --Index
@ListType int = null, --列表类型 0为标题列表 1为摘要列表 2为全文列表
@LinkUrl nvarchar = null, --分类链接
@MorePageUrl nvarchar = null, --
@AdList int = null, --列表广告位ID
@AdArticle int = null, --内容广告位
@AdPosition1 int = null, --广告位1
@AdPosition2 int = null, --广告位2
@AdPosition3 int = null, --广告位3
@AdPosition4 int = null --广告位4
)
AS
SET NOCOUNT ON
IF @DoCount = 1
BEGIN
IF @SearchUsingOR = 0
BEGIN
SELECT COUNT(*) FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is null OR [CategoryID] = @CategoryID ) --
AND (@Name is null OR [Name] = @Name ) --分类名
AND (@ParentID is null OR [ParentID] = @ParentID ) --父分类ID [GetList]
AND (@SortID is null OR [SortID] = @SortID ) --
AND (@MetaKeyword is null OR [MetaKeyword] = @MetaKeyword ) --
AND (@MetaDescription is null OR [MetaDescription] = @MetaDescription ) --
AND (@FolderName is null OR [FolderName] = @FolderName ) --
AND (@IndexPageName is null OR [IndexPageName] = @IndexPageName ) --Index
AND (@ListType is null OR [ListType] = @ListType ) --列表类型 0为标题列表 1为摘要列表 2为全文列表
AND (@LinkUrl is null OR [LinkUrl] = @LinkUrl ) --分类链接
AND (@MorePageUrl is null OR [MorePageUrl] = @MorePageUrl ) --
AND (@AdList is null OR [AdList] = @AdList ) --列表广告位ID
AND (@AdArticle is null OR [AdArticle] = @AdArticle ) --内容广告位
AND (@AdPosition1 is null OR [AdPosition1] = @AdPosition1 ) --广告位1
AND (@AdPosition2 is null OR [AdPosition2] = @AdPosition2 ) --广告位2
AND (@AdPosition3 is null OR [AdPosition3] = @AdPosition3 ) --广告位3
AND (@AdPosition4 is null OR [AdPosition4] = @AdPosition4 ) --广告位4
END
ELSE
BEGIN
SELECT COUNT(*) FROM [dbo].[Qst_Category]
WHERE
(@CategoryID is not null And [CategoryID] = @CategoryID) --
OR (@Name is not null And [Name] = @Name) --分类名
OR (@ParentID is not null And [ParentID] = @ParentID) --父分类ID [GetList]
OR (@SortID is not null And [SortID] = @SortID) --
OR (@MetaKeyword is not null And [MetaKeyword] = @MetaKeyword) --
OR (@MetaDescription is not null And [MetaDescription] = @MetaDescription) --
OR (@FolderName is not null And [FolderName] = @FolderName) --
OR (@IndexPageName is not null And [IndexPageName] = @IndexPageName) --Index
OR (@ListType is not null And [ListType] = @ListType) --列表类型 0为标题列表 1为摘要列表 2为全文列表
OR (@LinkUrl is not null And [LinkUrl] = @LinkUrl) --分类链接
OR (@MorePageUrl is not null And [MorePageUrl] = @MorePageUrl) --
OR (@AdList is not null And [AdList] = @AdList) --列表广告位ID
OR (@AdArticle is not null And [AdArticle] = @AdArticle) --内容广告位
OR (@AdPosition1 is not null And [AdPosition1] = @AdPosition1) --广告位1
OR (@AdPosition2 is not null And [AdPosition2] = @AdPosition2) --广告位2
OR (@AdPosition3 is not null And [AdPosition3] = @AdPosition3) --广告位3
OR (@AdPosition4 is not null And [AdPosition4] = @AdPosition4) --广告位4
END
End
ELSE
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * (@PageIndex-1)
SET @PageUpperBound = @PageLowerBound + @PageSize
IF @SearchUsingOR = 0
BEGIN
Select * from (Select TOP (@PageUpperBound) ROW_NUMBER() OVER (ORDER BY CategoryID DESC) AS RowIndex,* FROM
[dbo].[Qst_Category]
WHERE
(@CategoryID is null OR [CategoryID] = @CategoryID ) --
AND (@Name is null OR [Name] = @Name ) --分类名
AND (@ParentID is null OR [ParentID] = @ParentID ) --父分类ID [GetList]
AND (@SortID is null OR [SortID] = @SortID ) --
AND (@MetaKeyword is null OR [MetaKeyword] = @MetaKeyword ) --
AND (@MetaDescription is null OR [MetaDescription] = @MetaDescription ) --
AND (@FolderName is null OR [FolderName] = @FolderName ) --
AND (@IndexPageName is null OR [IndexPageName] = @IndexPageName ) --Index
AND (@ListType is null OR [ListType] = @ListType ) --列表类型 0为标题列表 1为摘要列表 2为全文列表
AND (@LinkUrl is null OR [LinkUrl] = @LinkUrl ) --分类链接
AND (@MorePageUrl is null OR [MorePageUrl] = @MorePageUrl ) --
AND (@AdList is null OR [AdList] = @AdList ) --列表广告位ID
AND (@AdArticle is null OR [AdArticle] = @AdArticle ) --内容广告位
AND (@AdPosition1 is null OR [AdPosition1] = @AdPosition1 ) --广告位1
AND (@AdPosition2 is null OR [AdPosition2] = @AdPosition2 ) --广告位2
AND (@AdPosition3 is null OR [AdPosition3] = @AdPosition3 ) --广告位3
AND (@AdPosition4 is null OR [AdPosition4] = @AdPosition4 ) --广告位4
) as Pager
where
Pager.RowIndex > @PageLowerBound
and Pager.RowIndex <= @PageUpperBound
END
ELSE
BEGIN
Select * from (Select TOP (@PageUpperBound) ROW_NUMBER() OVER (ORDER BY CategoryID DESC) AS RowIndex,* FROM
[dbo].[Qst_Category]
WHERE
(@CategoryID is not null And [CategoryID] = @CategoryID) --
OR (@Name is not null And [Name] = @Name) --分类名
OR (@ParentID is not null And [ParentID] = @ParentID) --父分类ID [GetList]
OR (@SortID is not null And [SortID] = @SortID) --
OR (@MetaKeyword is not null And [MetaKeyword] = @MetaKeyword) --
OR (@MetaDescription is not null And [MetaDescription] = @MetaDescription) --
OR (@FolderName is not null And [FolderName] = @FolderName) --
OR (@IndexPageName is not null And [IndexPageName] = @IndexPageName) --Index
OR (@ListType is not null And [ListType] = @ListType) --列表类型 0为标题列表 1为摘要列表 2为全文列表
OR (@LinkUrl is not null And [LinkUrl] = @LinkUrl) --分类链接
OR (@MorePageUrl is not null And [MorePageUrl] = @MorePageUrl) --
OR (@AdList is not null And [AdList] = @AdList) --列表广告位ID
OR (@AdArticle is not null And [AdArticle] = @AdArticle) --内容广告位
OR (@AdPosition1 is not null And [AdPosition1] = @AdPosition1) --广告位1
OR (@AdPosition2 is not null And [AdPosition2] = @AdPosition2) --广告位2
OR (@AdPosition3 is not null And [AdPosition3] = @AdPosition3) --广告位3
OR (@AdPosition4 is not null And [AdPosition4] = @AdPosition4) --广告位4
) as Pager
where
Pager.RowIndex > @PageLowerBound
and Pager.RowIndex <= @PageUpperBound
END
END
GO
最新评论