Kỹ thuật phân trang trong Database

Phần này của ông bạn tôi viết, gửi cho tôi theo kiểu khuyến mại. Đôi khi mình cũng lười thế là dùng luôn. Nhưng đọc kỹ lại phát hiện một số những điểm nên cài tiến.
1. Không sử dụng câu lệnh CREATE TABLE như trong này.
mà nên dùng DECLARE @tblTemp TABLE(id INT PRIMARY KEY, ...)

2. Không nên khai báo con trỏ trong SQL theo kiểu như thế này.
mà nên khai báo thế này thêm các từ khóa như readonly,...

bên cạnh đó cũng còn một kỹ thuật phân trang khác bằng cách sử dụng con trỏ. Tôi sẽ giới thiệu trong bài sau.

-------------------------------------------
if exists (select name from sysobjects where name = 'GetManyCUS_ResultBySearch')
drop proc GetManyCUS_ResultBySearch
go

create procedure GetManyCUS_ResultBySearch
@Keyword nvarchar(100),
@TypeID char(5),
@PageSize int,
@PageNumber int,
@ItemCount int OUT

as
begin

declare @sqlNewsSelect nvarchar(max),
@sqlDocumentSelect nvarchar(max),
@sqlProjectFDISelect nvarchar(max),
@sqlProjectODASelect nvarchar(max),
@sqlNewsTerm nvarchar(max),
@sqlDocumentTerm nvarchar(max),
@sqlProjectFDITerm nvarchar(max),
@sqlProjectODATerm nvarchar(max)

--------------------------------------------------------------------
/*Cac bieu thuc select*/
--------------------------------------------------------------------
set @sqlNewsSelect = '
select
a.NewsTitle as ''Title'',
cast(a.NewsDescription as nvarchar(250)) as ''Description'',
'''' as ''PathFile'',
b.TypeID as ''TypeID''
from CMS_News a
inner join CMS_Category b on a.CategoryID = b.CategoryID
where b.TypeID = ''news''
'
set @sqlDocumentSelect = '
select
a.DocumentName as ''Title'',
cast(a.DocumentDescription as nvarchar(250)) as ''Description'',
a.DocumentFilePath as ''PathFile'',
b.TypeID as ''TypeID''
from CMS_Document a
inner join CMS_Category b on a.CategoryID = b.CategoryID
where b.TypeID = ''docum''
'
set @sqlProjectFDISelect = '
select
a.ProjectFDIName as ''Title'',
cast(a.FDIDetailContent as nvarchar(250)) as ''Description'',
a.FDIDocumentPath as ''PathFile'',
b.TypeID as ''TypeID''
from CMS_ProjectFDI a
inner join CMS_Category b on a.CategoryID = b.CategoryID
where b.TypeID = ''proje''
'
set @sqlProjectODASelect = '
select
a.ProjectODAName as ''Title'',
cast(a.ODADetailContent as nvarchar(250)) as ''Description'',
a.ODADocumentPath as ''PathFile'',
b.TypeID as ''TypeID''
from CMS_ProjectODA a
inner join CMS_Category b on a.CategoryID = b.CategoryID
where b.TypeID = ''proje''
'
--------------------------------------------------------------------
/*Phan tich chuoi keyword dau vao thanh cac keyword nho*/
--------------------------------------------------------------------

set @sqlNewsTerm = ' 1 != 1'
set @sqlDocumentTerm = ' 1 != 1'
set @sqlProjectFDITerm = ' 1 != 1'
set @sqlProjectODATerm = ' 1 != 1'

create table #Keyword(Keyword nvarchar(100))
declare @sep nvarchar(10)
set @sep = ' '
declare @keywordTemp nvarchar(500)
set @keywordTemp = replace(LTRIM(RTRIM(@Keyword))+' ','''','''''')
while(CHARINDEX(@sep + @sep, @keywordTemp COLLATE Latin1_General_CS_AS) > 0)
set @keywordTemp = replace(@keywordTemp, @sep + @sep, @sep)
declare @indexBegin int
declare @indexEnd int
set @indexBegin = 1
while(CHARINDEX ( @sep,@keywordTemp COLLATE Latin1_General_CI_AS, @indexBegin) > 0)--Latin1_General_CS_AS
begin
set @indexEnd = CHARINDEX ( @sep,@keywordTemp COLLATE Latin1_General_CI_AS, @indexBegin)
insert into #Keyword values (SUBSTRING(@keywordTemp, @indexBegin, @indexEnd - @indexBegin))
set @indexBegin = @indexEnd + 1
end
declare @KeywordExec nvarchar(500)
declare rm_cursor cursor for
select
Keyword
from
#Keyword
open rm_cursor
fetch next from rm_cursor into
@KeywordExec
while (@@FETCH_STATUS = 0)
begin
--------------------------------------------------------------------
/*Dua cac tu tim duoc trong chuoi de ghep thanh bieu thuc tim kiem*/
--------------------------------------------------------------------
set @KeywordExec = 'N''%' + @KeywordExec + '%'''
set @sqlNewsTerm = @sqlNewsTerm + ' or a.NewsTitle like ' + @KeywordExec
set @sqlDocumentTerm = @sqlDocumentTerm + ' or a.DocumentName like ' + @KeywordExec
set @sqlProjectFDITerm = @sqlProjectFDITerm + ' or a.ProjectFDIName like ' + @KeywordExec
set @sqlProjectODATerm = @sqlProjectODATerm + ' or a.ProjectODAName like ' + @KeywordExec
fetch next from rm_cursor into @KeywordExec
end
close rm_cursor
deallocate rm_cursor
drop table #Keyword

declare @sql nvarchar(max)
set @sql = '
select
row_number() over( order by Title) as RowNumber,
Title,
Description,
PathFile,
TypeID
from ('
+ @sqlNewsSelect + ' and (' + @sqlNewsTerm + ')'
+ ' union '
+ @sqlDocumentSelect + ' and (' + @sqlDocumentTerm + ')'
+ ' union '
+ @sqlProjectFDISelect + ' and (' + @sqlProjectFDITerm + ')'
+ ' union '
+ @sqlProjectODASelect + ' and (' + @sqlProjectODATerm + ')'
+') as tblSelect'

insert into loghiep values(@sql)

declare @sqlcount nvarchar(max)
set @sqlcount = 'select count(*) from ('+@sql+') as tbl_Result'
create table #ItemCount(ItemCount int)
insert into #ItemCount exec(@sqlcount)
set @ItemCount = (select top(1) ItemCount from #ItemCount)
drop table #ItemCount
declare @ItemMin int, @ItemMax int
set @ItemMin = @PageSize * @PageNumber - @PageSize + 1
set @ItemMax = @PageSize * @PageNumber + 1
if @ItemMin < 0 set @ItemMin = 0
if @ItemMax < 0 set @ItemMax = 0
set @sql = 'select
Title,
Description,
PathFile,
TypeID
from ('+@sql+') as tbl_Result where 1 = 1'
set @sql = @sql + ' and RowNumber >= ' + convert(varchar(10), @ItemMin)
set @sql = @sql + ' and RowNumber < ' + convert(varchar(10), @ItemMax)
exec(@sql)

end
go

grant execute on GetManyCUS_ResultBySearch to public
go