一个asp.net调用存储过程来分页的代码-优快云博客

-- =============================================
-- 数据分页的存储过程
-- 记录号在 TempIDKey_Num 字段中
-- 调用的例子：表示从结果中第3行开始的5条记录。
--   T-SQL：EXECUTE proTest N'select top 100 percent * from orders', 3,5
--   ASP.NET(C#)：
--      <%@ Page Language="C#" %>
--      <%@ Import Namespace="System.Data" %>
--      <%@ Import Namespace="System.Data.SqlClient" %>
--      <Script Runat="Server">
--      void Page_Load( Object s, EventArgs e )
--      {
--          SqlConnection myConnection;
--          SqlCommand myCommand;
--          myConnection = new SqlConnection( "Server=(local);uid=sa;pwd=11;Database=Northwind" );
--          myCommand = new SqlCommand( "proTest", myConnection );
--          myCommand.CommandType = CommandType.StoredProcedure;
--          myCommand.Parameters.Add("@strSql","Select top 30 * from orders");
--          myCommand.Parameters.Add("@startRow",10);
--          myCommand.Parameters.Add("@maxRows",15);
--          myConnection.Open();
--          myDataGrid.DataSource = myCommand.ExecuteReader();
--          myDataGrid.DataBind();
--          myConnection.Close();
--      }
--      </Script>
--      <html><head><title>DataGrid</title></head><body>
--      <form Runat="Server">
--      <asp:DataGrid id="myDataGrid" Runat="Server"/>
--      </form>
--      </body></html>
-- =============================================

IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE name = N'proTest'
       AND    type = 'P')
    DROP PROCEDURE proTest
GO
CREATE PROCEDURE proTest
    @strSql as nvarchar(2000) = null, --要查询语句如 Select top 30 * from orders
    @startRow as int = null,        --从其开始的从零开始的记录号
    @maxRows as int = null          --要检索的最大记录数
AS
    DECLARE @stopRow as int
    set @stopRow = @startRow + @maxRows

    set @strSql = N' Select top ' + CAST(@StopRow as nvarchar(9)) + '*, IDENTITY(int,1,1) AS TempIDKey_Num '
                 + ' INTO #New_Table '
                 + ' FROM( ' + @strSql + ') A '
                 + ' Select * From #New_Table Where TempIDKey_Num>=' + CAST(@StartRow as nvarchar(9))
                 + ' DROP TABLE #New_Table '
    execute sp_executesql @strSql