📄 分页存储过程.txt
字号:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/***********************************************************
过程名称： NAD_GetAdvertisementInfoList
功能描述： 获取NAD主表Advertisement清单,并获取总记录条数
参数说明：
编写日期： 2005/08/31
程序作者： Jerry
调试记录： exec NAD_GetAdvertisementInfoList '0210'
修改记录：
************************************************************/
CREATE proc TestNAD_GetAdvertisementInfoList
-------------查询条件-------------
@AdCode varchar(1024) = '', -- 广告编号
@HeaderLabel varchar(200) = '', -- 广告主题
@Celebrities varchar(100) = '', -- 广告代言人
@MediumID varchar(1024) = '', -- 媒体编号
@MediumType varchar(30) = '', -- 媒体类型
@MediumGeo varchar(1024) = '', -- 媒体所属地区编号
@MagazineType varchar(256) = '', -- 杂志类型
@ColorType varchar(30) = '', -- 平面广告色彩类型，如黑白，双色等
@LayoutStyle varchar(100) = '', -- 平面广告杂志样式布局类型
@AdvertiserID varchar(1024) = '', -- 广告主编号
@AdvertiserGeo varchar(1024) = '', -- 广告主所属地区编号
@AdvertiserName varchar(200) = '', -- 广告主名称
@BrandCode varchar(1024) = '', -- 品牌编号
@BrandName varchar(200) = '', -- 品牌名称
@CategoryCode varchar(1024) = '', -- 产品分类编号
@AdObject varchar(40) = '', -- 广告客体类型
@ProductModel varchar(200) = '', -- 产品名称或者产品型号
@SDate varchar(30) = '', -- 搜索起始时间
@EDate varchar(30) = '', -- 搜索结束时间
@OrderByField varchar(100) = 'AuditTime DESC', -- 排序字段与排序规则，如AuditTime DESC,AdvertisementCode ASC,中间用逗号隔开
--------------------------------------
@PageIndex int = 1, -- 页码
@PageSize int = 500, -- 每页记录条数,默认每页取500条数据,根据需求改动
--------------------------------------
@HeaderType varchar(6) ='false', -- HeaderLabel字段使用Like或Contains条件判断
@CeleType varchar(6) ='false', -- Celebrities字段使用Like或Contains条件判断
@AderType varchar(6) ='false', -- AdvertiserName字段使用Like或Contains条件判断
@BrandType varchar(6) ='false', -- BrandName字段使用Like或Contains条件判断
@ProType varchar(6) ='false' -- ProductModel字段使用Like或Contains条件判断
AS
declare @strWhere varchar(6000) -- 查询Where条件
declare @intKeyPos int -- 某字符串在另一字符串的位置
declare @fldName varchar(30) -- 主排序字段
declare @strTmp varchar(100) -- 临时变量
declare @strSQL varchar(8000) -- 主SQL语句
declare @strOrder varchar(200) -- 排序字符串
declare @tempStr varchar(4000)
---declare @PageSize int -- 每页记录条数
---Set @PageSize = 500 -- 默认每页取500条数据,根据需求改动
Set @tempStr = ''
--------------初始化Where条件-----------------
Set @strWhere=' (DataStatus=4) '
If (@AdCode IS Not NULL) AND (@AdCode <> '')
Set @strWhere = @strWhere +' AND ( AdCode in ('+@AdCode+') )'
If (@HeaderLabel IS NOT NULL) AND (@HeaderLabel <> '')
Begin
if (@HeaderType = 'true')
begin
Set @HeaderLabel = REPLACE(@HeaderLabel,'|','" And "')
Set @HeaderLabel = '"' +@HeaderLabel +'"'
Set @strWhere = @strWhere+' AND ((Contains(HeaderLabel,'''+@AdCode+''')))'
end
else if (@HeaderType = 'false')
begin
Set @HeaderLabel = REPLACE(@HeaderLabel,'|','%'' And HeaderLabel Like ''%')
Set @strWhere = @strWhere+' AND (HeaderLabel Like ''%'+@HeaderLabel+'%'')'
end
End
If (@Celebrities IS NOT NULL) AND (@Celebrities <> '')
Begin
if (@CeleType = 'true')
begin
Set @Celebrities = REPLACE(@Celebrities,',','" Or "')
Set @Celebrities = '"' +@Celebrities +'"'
Set @strWhere = @strWhere +' AND ((Contains(Celebrities,'''+@Celebrities+''')))'
end
else if (@CeleType = 'false')
begin
Set @Celebrities = REPLACE(@Celebrities,',','%'' And Celebrities Like ''%')
Set @strWhere = @strWhere +' Or (Celebrities Like ''%'+@Celebrities+'%'')'
end
End
If (@MediumID IS NOT NULL) AND (@MediumID <> '')
Set @tempStr = @tempStr+'Or ( MediumID in ('+@MediumID+') )'
If (@MediumType IS NOT NULL) AND (@MediumType <> '')
Set @tempStr = @tempStr+'Or ( MediumType in ('+@MediumType+') )'
If (@MediumGeo IS NOT NULL) AND (@MediumGeo <> '')
Set @tempStr = @tempStr+'Or ( MediumID in (select MediumCode from dbo.MediumInfo where GeoCode in ('+@MediumGeo+')) )'
If (@MagazineType IS NOT NULL) AND (@MagazineType <> '')
Set @tempStr = @tempStr+'Or ( MediumID in (select MediumCode from dbo.MediumInfo where MagazineType in('+@MagazineType+')) )'
If (Len(@tempStr)>10)
Begin
Set @tempStr = SubString(@tempStr,4,Len(@tempStr)-3)
Set @strWhere = @strWhere +' AND ('+@tempStr+')'
End
If (@ColorType IS NOT NULL) AND (@ColorType <> '')
Set @strWhere = @strWhere +' AND ( ColorType in ('+@ColorType+') )'
If (@LayoutStyle IS NOT NULL) AND (@LayoutStyle <> '')
Set @strWhere = @strWhere +' AND ( LayoutStyle in ('+@LayoutStyle+') )'
If (@AdvertiserID IS NOT NULL) AND (@AdvertiserID <> '')
Set @strWhere = @strWhere +' AND ( AdvertiserID in ('+@AdvertiserID+') )'
If (@AdvertiserGeo IS NOT NULL) AND (@AdvertiserGeo <> '')
Set @strWhere = @strWhere +' AND ( AdvertiserID in (select AdvertiserCode from dbo.AdvertiserInfo where GeoCode in ('+@AdvertiserGeo+')) )'
If (@AdvertiserName IS NOT NULL) AND (@AdvertiserName <> '')
Begin
if (@AderType = 'true')
begin
Set @AdvertiserName = REPLACE(@AdvertiserName,',','" And "')
Set @AdvertiserName = '"' +@AdvertiserName +'"'
Set @strWhere = @strWhere +' AND (( Contains(AdvertiserName,'''+@AdvertiserName+''')))'
end
else if (@AderType = 'false')
begin
Set @AdvertiserName = REPLACE(@AdvertiserName,',','%'' And AdvertiserName Like ''%')
Set @strWhere = @strWhere +' AND (AdvertiserName Like ''%'+@AdvertiserName+'%'')'
end
End
If (@BrandCode IS NOT NULL) AND (@BrandCode <> '')
Set @strWhere = @strWhere +' AND ( BrandCode in ('+@BrandCode+') )'
If (@BrandName IS NOT NULL) AND (@BrandName <> '')
Begin
if (@BrandType = 'true')
begin
Set @BrandName = REPLACE(@BrandName,',','" And "')
Set @BrandName = '"' +@BrandName +'"'
Set @strWhere = @strWhere +' AND (( Contains(BrandName,'''+@BrandName+''')))'
end
else if (@BrandType = 'false')
begin
Set @BrandName = REPLACE(@BrandName,',','%'' And BrandName Like ''%')
Set @strWhere = @strWhere +' AND (BrandName Like ''%'+@BrandName+'%'')'
end
End
If (@CategoryCode IS NOT NULL) AND (@CategoryCode <> '')
Set @strWhere = @strWhere +' AND ( CategoryCode in (select CategoryCode from dbo.CategoryList Where CategoryCode in ('+@CategoryCode+') or CategoryCode in (select CategoryCode from dbo.CategoryList where ParentCode in ('+@CategoryCode+')) or CategoryCode in (select CategoryCode from dbo.CategoryList where ParentCode in(select CategoryCode from dbo.CategoryList where ParentCode in ('+@CategoryCode+')))) )'
If (@AdObject IS NOT NULL) AND (@AdObject <> '')
Set @strWhere = @strWhere +' AND ( AdObject in ('+@AdObject+') )'
/******************取消的条件********************
If (@ProductName IS NOT NULL) AND (@ProductName <> '')
Begin
Set @ProductName = REPLACE(@ProductName,',','" OR "')
Set @ProductName = '"' +@ProductName +'"'
Set @strWhere = @strWhere +' AND (( Contains(ProductName,'''+@ProductName+''')))'
End
**************************************************/
If (@ProductModel IS NOT NULL) AND (@ProductModel <> '')
Begin
if (@ProType = 'true')
begin
Set @ProductModel = REPLACE(@ProductModel,',','" Or "')
Set @ProductModel = '"' +@ProductModel +'"'
Set @strWhere = @strWhere +' AND (( Contains(ProductModel,'''+@ProductModel+''')))'
end
else if (@ProType = 'false')
begin
Set @ProductModel = REPLACE(@ProductModel,',','%'' Or ProductModel Like ''%')
Set @strWhere = @strWhere +' AND (ProductModel Like ''%'+@ProductModel+'%'')'
end
End
--交换时间,假如结束时间小于起始时间则相互交换时间
If ((@SDate IS NOT NULL) AND (@SDate <> '')) AND ((@EDate IS NOT NULL) AND (@EDate <> '')) AND ( DATEDIfF(DAY,@SDate,@EDate)<0 )
Begin
Declare @tempTime varchar(30)
Set @tempTime = @SDate
Set @SDate = @EDate
Set @EDate = @tempTime
End
If (@SDate IS NOT NULL) AND (@SDate <> '')
Set @strWhere = @strWhere + ' AND ( ReleaseDate >= '''+@SDate+''' )' If (@EDate IS NOT NULL) AND (@EDate <> '')
Set @strWhere = @strWhere + ' AND ( ReleaseDate <= '''+@EDate+''' )'
-------------------------------------------------------------------------------
--排序字段默认为AuditTime DESC，按照审核时间降序
If (@OrderByField IS NULL) OR (@OrderByField = '')
Set @OrderByField = 'AuditTime DESC'
--计算记录总数
If ( Len(@strWhere) > 20 )
Set @strSQL='Select count(*) as RecordCount From dbo.Advertisement where '+@strWhere
Else
Set @strSQL='Select count(*) as RecordCount From dbo.Advertisement where DataStatus=4'
--输出
--Print(@strSQL)
--执行SQL语句
Exec (@strSQL)
-- 主排序字段的处理
Set @strTmp = Ltrim( @OrderByField )
Set @intKeyPos = CharIndex( ',', @strTmp )
-- 若包含多个排序字段，仅取一个
If @intKeyPos > 0
Set @strTmp = Left( @strTmp, @intKeyPos -1 )
-- 解析排序的字段名
Set @intKeyPos = CharIndex( CHAR(32), @strTmp )
If @intKeyPos < 1
Set @intKeyPos = CharIndex( CHAR(9), @strTmp )
If @intKeyPos < 1
Set @fldName = @strTmp
Else
Set @fldName = Left( @strTmp, @intKeyPos -1 )
-- 解析排序方式
Set @strTmp = Substring( @strTmp, @intKeyPos + 1, Len( @strTmp ) - @intKeyPos + 1 )
Set @strTmp = Upper( Rtrim( Ltrim( @strTmp ) ) )
If @strTmp = 'DESC'
Set @strTmp = '< ( Select MIN' -- 降序
Else
Set @strTmp = '>= ( Select MAX' -- 升序
-- 连接排序语句字符串
Set @strOrder = ' Order by ' + @OrderByField
--如果是第一页就直接返回该页数据
If @PageIndex = 1
begin
If ( Len(@strWhere) > 20 )
Set @strSQL = 'select top '+ str(@PageSize) +' AdCode,HeaderLabel,AdvertiserID,AdvertiserName,MediumID,MediumName,BrandCode,BrandName,MediumType,ReleaseDate,PageNumber,LayoutStyle,ProductModel,CategoryCode,CategoryName,GraphicPath,GraphicFile from dbo.Advertisement where ' + @strWhere +' ' + @strOrder
Else
Set @strSQL = 'select top '+ str(@PageSize) +' AdCode,HeaderLabel,AdvertiserID,AdvertiserName,MediumID,MediumName,BrandCode,BrandName,MediumType,ReleaseDate,PageNumber,LayoutStyle,ProductModel,CategoryCode,CategoryName,GraphicPath,GraphicFile from dbo.Advertisement where DataStatus=4'+ @strOrder
End
Else
begin
Set @strSQL = 'select top ' + str(@PageSize) +' AdCode,HeaderLabel,AdvertiserID,AdvertiserName,MediumID,MediumName,BrandCode,BrandName,MediumType,ReleaseDate,PageNumber,LayoutStyle,ProductModel,CategoryCode,CategoryName,GraphicPath,GraphicFile from dbo.Advertisement
where DataStatus=4 and (' + @fldName + ')' + @strTmp + '(('+ @fldName + ')) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ('+ @fldName + ') from dbo.Advertisement where DataStatus=4 ' + @strOrder + ') as tblTmp)'+ @strOrder
If ( Len(@strWhere) > 20 )
Set @strSQL = 'select top ' + str(@PageSize) +' AdCode,HeaderLabel,AdvertiserID,AdvertiserName,MediumID,MediumName,BrandCode,BrandName,MediumType,ReleaseDate,PageNumber,LayoutStyle,ProductModel,CategoryCode,CategoryName,GraphicPath,GraphicFile from dbo.Advertisement
where DataStatus=4 and (' + @fldName + ')' + @strTmp + '(('
+ @fldName + ')) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ('
+ @fldName + ') from dbo.Advertisement where DataStatus=4 and ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere +' ' + @strOrder
end
--输出
--Print(@strSQL)
--执行SQL语句
Exec (@strSQL)
--返回分类表
Exec('Select CategoryCode,CategoryName,ParentCode,FullPath From dbo.CategoryList where DataStatus=2')
--返回辅助信息
Exec('Select AutoID,TrueValue,DisplayText,DataFlag From dbo.FixedDataList Where DataStatus=2 and (DataFlag=1 or DataFlag=2 or DataFlag=3) Order by DataFlag')
--创建临时表,取出MediumID与BrandCode
ALTER Table [dbo].[#tempTable1] (
[MediumID] [int]
) ON [PRIMARY]
Create Table [dbo].[#tempTable2] (
[BrandCode] [int]
) ON [PRIMARY]
--插入临时表数据
Exec(
'Insert Into #tempTable1
Select
Distinct(MediumID)
From
dbo.Advertisement
With (nolock)
Where ' + @strWhere
)
Exec(
'Insert Into #tempTable2
Select
Distinct(BrandCode)
From
dbo.Advertisement
With (nolock)
Where ' + @strWhere
)
--获取媒体中英文名称信息
Exec('Select MediumCode,CHName,EnName from dbo.MediumInfo where MediumCode in (Select MediumID From #tempTable1)')
--获取品牌中英文名称信息
Exec('Select BrandCode,CHName,EnName from dbo.BrandList where BrandCode in (Select BrandCode From #tempTable2)')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -