sql存储过程分页查询

来源：互联网发布：手机淘宝如何看总消费编辑：程序博客网时间：2024/04/28 03:23

CREATE PROCEDURE [dbo].[pr_Page_More] @Key varchar(50),--根据字段查询数量@Col varchar(50),--排序方式@Cols varchar(2000),--表字段@TabName varchar(300),--表名@Where varchar(1000),--条件@Page int,--当前页码@Val int,--每页显示多少页ASBEGINSET NOCOUNT ON;declare @P_begin int,@P_end int,@Txt varchar(5000)set @P_begin=@Page*@Valset @P_end=@P_begin+@Valset @Txt='select * from(select ROW_NUMBER() OVER (order by '+@Col+') as Num,'+@Cols+' from '+@TabName+' where 1=1 '+@Where+')as Tab where Tab.Num>'+CONVERT(varchar(10),@P_begin)+' and Tab.Num<='+CONVERT(varchar(10),@P_end)exec(@Txt)set @Txt='select count('+@Key+') from '+@TabName+' where 1=1 '+@Whereexec(@Txt)ENDGO

执行操作

public DataSet TagList(string key, string Order, string tabName, string Where, int Page, int Val){            SqlParameter[] parameters = {new SqlParameter("@Key", SqlDbType.VarChar,50),                    new SqlParameter("@Col", SqlDbType.VarChar,50),                    new SqlParameter("@Cols", SqlDbType.VarChar,100),                    new SqlParameter("@TabName", SqlDbType.VarChar,100),                    new SqlParameter("@Where", SqlDbType.VarChar,1000),                    new SqlParameter("@Page", SqlDbType.Int),                    new SqlParameter("@Val", SqlDbType.Int)};            parameters[0].Value = key;            parameters[1].Value = Order;            parameters[2].Value = "*";            parameters[3].Value = tabName;            parameters[4].Value = Where;            parameters[5].Value = Page;            parameters[6].Value = Val;            DataSet ds = SqlHelper.RunProcedure("pr_Page", parameters);            return ds;｝