求一个分页的SQL语句
解决方案 »
- 遍历页面html控件怎写
- 有关INSTR的简单问题
- 如何打开vs2008的命令工具,转化一个dll
- 有个问题很困扰,就是论坛贴图开放后,如果有人在图片中用iframe挂马怎么办?
- 错误提示:No relevant source lines, 会的讲解不会的捧个人场--谢谢啦
- Repeater的问题,高手帮我下吧。
- 请高手看看,下面的sql语句,第一个答对的给分,在线等
- asp.net生成静态页面后如何统计点击数
- 在JavaScript中怎样得到配置文件中的内容
- DropDownList控件问题??????
- 高分求助(分不够再加 要多少都行)
- 我在网页上发邮件,没有发送者,怎么发送邮件,注意没有发送邮箱。。。。。。。
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(newsid) from wqnews
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select newsid from wqnews order by addtime desc
select O.newsid,O.source,O.heading,O.addtime from wqnews O,@indextable t where O.newsid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount offGO
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO这是一个不错的分页存储过程,由于每次在页面中调用比较麻烦,就写了个针对此调用存储过程的Module :
Module MdlCommon
Public DBSet As DataSet '查询得到的记录集
Public ErrorMsg As String '存放错误信息
Public Pages As Integer Public Function GetResult(ByVal tb As String, ByVal col As String, ByVal collist As String, ByVal condition As String, ByVal coltype As Integer, ByVal orderby As Integer, ByVal Page As Integer, ByVal Pagesize As Integer, ByRef errMsg As String) As DataSet
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet
errMsg = ""
Try
cnn = New SqlClient.SqlConnection("data source=(local);initial catalog=Northwind;user id=sa;pwd='hyaocuk!'")
cmd = New SqlClient.SqlCommand("sp_page", cnn)
cmd.CommandType = CommandType.StoredProcedure
'给存储过程的参数赋值
Dim sp_temp As SqlClient.SqlParameter
sp_temp = cmd.Parameters.Add("@tb", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = tb sp_temp = cmd.Parameters.Add("@col", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = col sp_temp = cmd.Parameters.Add("@collist", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = collist sp_temp = cmd.Parameters.Add("@condition ", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = condition sp_temp = cmd.Parameters.Add("@pagesize", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Pagesize sp_temp = cmd.Parameters.Add("@page", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Page sp_temp = cmd.Parameters.Add("@orderby", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = orderby sp_temp = cmd.Parameters.Add("@coltype", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = coltype sp_temp = cmd.Parameters.Add("@pages", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Output adpt = New SqlClient.SqlDataAdapter
adpt.SelectCommand = cmd
adpt.Fill(rst)
GetResult = rst
Pages = cmd.Parameters("@pages").Value
Catch ex As Exception
errMsg = ex.Message
Finally
rst = Nothing
cnn = Nothing
End Try
End Function
End Module
调用时候:
Private Sub dgSortReport_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgSortReport.ItemCreated
If e.Item.ItemType = ListItemType.Pager Then
Dim pager As TableCell = CType(e.Item.Controls(0), TableCell)
Dim i As Integer
For i = 0 To pager.Controls.Count Step 2
Dim o As Object = CType(pager.Controls(i), Object)
If TypeOf (o) Is LinkButton Then
Dim h As LinkButton = CType(o, LinkButton)
h.Text = "[ " + h.Text + " ]" Else
Dim l As Label = CType(o, Label)
l.Text = "第" + l.Text + "页"
End If
Next i
End If
End Sub
'绑定datagrid的函数
Protected Sub CreateDatasource(ByVal CurrentPage As Integer) Dim tb, col, collist, condition As String
'查询的表名
tb = "Orders"
'排序列的列名
col = "OrderID"
'返回的列名列表
collist = "EmployeeID,RequiredDate"
'查询的表件
condition = "OrderID>0" Dim coltype, orderby, pagesize, page As Integer
coltype = 0
orderby = 0
pagesize = 10
page = CurrentPage
DBSet = GetResult(tb, col, collist, condition, coltype, orderby, page, pagesize, ErrorMsg)
dgSortReport.VirtualItemCount = Pages
dgSortReport.DataSource = DBSet.Tables(0).DefaultView
dgSortReport.DataBind()
End Sub
Private Sub dgSortReport_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgSortReport.PageIndexChanged
dgSortReport.CurrentPageIndex = e.NewPageIndex
' 页码值是从零开始的( 所以要加一)
CreateDatasource(e.NewPageIndex + 1)
End Sub