刚写的一个用Not In 方法的分页,不知道,还有那些Bug,贴上来,希望大家一起找一下
ALTER Proc Xpage
(
@tableName nvarchar(100),--表名
@fields nvarchar(300)='*',--查寻字段: name,sex,age As '年龄'
@idField nvarchar(30),--Id
@whereFields nvarchar(200)='',--条件:age>10 And Sex=1
@orderFields nvarchar(200)='',--排序:id Asc,name Desc
@pageIndex int=1,
@pageSize int=15,
@doCount bit=0, --非零统计
@recordCount int out
)
As
Declare @SQL nvarChar(4000)
Declare @strTemp nvarChar(2000)
Declare @strCTemp nvarChar(1000)
---------------------------------
Set @strTemp=''
If @whereFields !=''
Begin
Set @strTemp=@strTemp + ' Where '+ @whereFields
Set @strCTemp=@strTemp
End
Else
Begin
Set @strCTemp=''
End
------------------------
If @orderFields !=''
Begin
Set @strTemp=@strTemp + ' Order By '+ @orderFields
End
Else
Begin
Set @strTemp=@strTemp + ' Order By '+ @idField
End
----------------------------------
--print @whereFields
---统计记录数
If @doCount !=0
Begin
Set @SQL=N'Select @recordCount=Count(' +@idField +') From '+ @tableName + ' ' + @strCTemp
Exec sp_Executesql @SQL,N'@recordCount int out',@recordCount out
--Set @SQL='Select count(*) as total From ['+ @tableName + '] ' + @strCTemp
--Print @SQL
--Execute( @SQL)
End
----分页
Else
Begin
Set @strCTemp=''
End
Begin
If @pageIndex=1
Set @SQL='Select Top '+str( @pageSize) + ' ' + @fields + ' From '+@tableName + @strTemp
Else
Begin
Set @SQL='Select '+ @fields +' From '
Set @SQL=@SQL +'(Select Top '+ str(@pageSize * @pageIndex) + ' ' +@fields +' From '+ @tableName +' ' +@strTemp + ' ) As t'
Set @SQL=@SQL + ' Where '+ @idField + ' Not In( ' + 'Select Top '+ str(@pageSize * (@pageIndex-1)) +' '+@idField +' From ' + @tableName +@strTemp + ') '
End
End
Exec(@SQL)