set quoted_identifier on go set ansi_nulls on go if exists (select * from sysobjects where name = 'sp_splitPage') drop procedure sp_splitPage go create procedure sp_splitPage ( @sqlstr nvarchar(4000),--查询字符串 @pagecount int,--第N页 @pagesize int--每页行数 ) as set nocount on declare @P1 int,--P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@pagecount as 当前页 set @pagecount=(@pagecount-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@pagecount,@pagesize exec sp_cursorclose @P1 go set quoted_identifier off go set ansi_nulls on go ----调用语句 exec sp_splitPage 'select * from S_AdManagement',2,150 ---要每页4行,第2页,2和4你可以自己修改 调用实例: private DataSet GetDataSource() { string conStr = "server=.;database=dbuser;uid=sa;pwd=000000"; DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(conStr)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "sp_splitpage"; cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Prepare(); cmd.Parameters.Add(new SqlParameter("@sqlstr", SqlDbType.VarChar, 200)); cmd.Parameters.Add(new SqlParameter("@pagecount", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@pagesize", SqlDbType.Int)); cmd.Parameters["@sqlstr"].Value = "select * from info"; cmd.Parameters["@pagecount"].Value = 2; cmd.Parameters["@pagesize"].Value = 4; SqlDataAdapter caChe = new SqlDataAdapter(cmd as SqlCommand); caChe.Fill(ds); } return ds; } 调用的时候需要注意了,因为上面的存储过程生成了三张表,所以下面填充的ds里面也会有三张表,需要用索引了选择。