?? 分頁存儲過程.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 + -