自定义分页存储过程 及VB.NET 调用

ALTER PROCEDURE [dbo].[GetRecordFromPage]
    @tblName      nvarchar(255),       -- 表名
    @fldName      nvarchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    int = 0,            -- 设置排序类型, 非0 值则降序
    @IsCount       int = 0, -- 返回记录总数, 非0 值则返回
     @PageCount int output,   --总页数，作为返回值
    @strWhere     nvarchar(255) = ''  -- 查询条件(注意: 不要加where)
AS
declare @i int,@RecordCount int
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strTmp   nvarchar(1000)       -- 临时变量
declare @strOrder nvarchar(500)        -- 排序类型



if @OrderType != 0

begin

    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName + '] desc'

end

else

begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by [' + @fldName +'] asc'
end


set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
    + @strOrder


if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1

begin
    set @strTmp = ''
    if @strWhere != ''
    set @strTmp = ' where (' + @strWhere + ')'

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
if @IsCount != 0
set @strSQL = 'select @i=count(' + @fldName + ') from [' + @tblName + '] where ' + @strWhere + ' '
exec   sp_executesql   @strSQL,N'@i   int   output' ,@i  output 
set @RecordCount = @i
/*得到总页数，注意使用convert先转换整型为浮点型，防止小数部分丢失*/
set @PageCount = ceiling (convert( float,@i)/@PageSize)
return @i



VB调用的过程，和使用
Private Sub PageSelect(ByVal tblName As String, ByVal fldName As String, ByVal PageSize As Integer,
                           ByVal PageIndex As Integer, ByVal OrderType As Integer, ByVal IsCount As Integer,
                           ByVal strWhere As String)
        Try
            Dim conn As New SqlConnection
            conn.ConnectionString = ConfigurationManager.ConnectionStrings("wingsbook").ToString()
            Dim myCommand As New SqlDataAdapter
            Dim ds As DataSet
            myCommand = New SqlDataAdapter("GetRecordFromPage", conn)
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
            myCommand.SelectCommand.Parameters.Clear()
            myCommand.SelectCommand.Parameters.Add("@tblName", SqlDbType.NVarChar, 255).Value = tblName
            myCommand.SelectCommand.Parameters.Add("@fldName", SqlDbType.NVarChar, 255).Value = fldName
            myCommand.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize
            myCommand.SelectCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex
            myCommand.SelectCommand.Parameters.Add("@OrderType", SqlDbType.Int).Value = OrderType
            myCommand.SelectCommand.Parameters.Add("@IsCount", SqlDbType.Int).Value = IsCount
            Dim PageCount As SqlParameter = myCommand.SelectCommand.Parameters.Add("@PageCount", SqlDbType.Int)
            PageCount.Direction = ParameterDirection.Output
            Dim CountS As SqlParameter = myCommand.SelectCommand.Parameters.Add("@CountS", SqlDbType.Int)
            CountS.Direction = ParameterDirection.ReturnValue
            myCommand.SelectCommand.Parameters.Add("@strWhere", SqlDbType.NVarChar, 255).Value = strWhere
            ds = New DataSet()
            myCommand.Fill(ds, "tableset")
            Me.DataGridView1.DataSource = ds.Tables(0)
            keepnum = PageCount.Value
            keepcount = CountS.Value
            countsum.Text = CountS.Value & " (总页数：" & PageCount.Value & ")"
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub


调用：

PageSelect("Com_Customer", "id", 100, 1, 0, 1, "1=1")

  

使用

declare @i int ,@PageCount int

exec @i= GetRecordFromPage 'Customer_info', 'id', 1000 ,2 , 1, 1 ,@PageCount output , 'id>14000'

select @PageCount as [out]

print @i