ALTER PROCEDURE GetDataList ( @TableName varchar(50), --表名 @Fields varchar(5000) = '*', --字段名(全部字段为*) @OrderField varchar(5000), --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int, --每页多少条记录 @pageIndex int = 1 , --指定当前为第几页 @TotalPage int output --返回总页数 ) as begin Begin Tran --开始事务 Declare @sql nvarchar(4000); Declare @totalRecord int; --计算总记录数if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'select @totalRecord = count(*) from ' + @TableName else set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName else set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况 if @PageIndex<=0 Set @pageIndex = 1if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord intset @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)Exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord ---返回记录总数 End end
不要传表明 字段名 where的这种形式。 CSDN的人回答问题都不看别人要求什么
//当前页第一条记录对应的行号 int start = (pageIndex - 1) * pageSize + 1; //当前页最后一条记录对应的行号 int end = start + pageSize - 1; string sql = "select * from " + "(select row_number() over(order by id) as n,* from Books) N_Books " + "where n between @start and @end"; List<Book> list = new List<Book>(); try { SqlParameter[] par = new SqlParameter[] { new SqlParameter("@start",start), new SqlParameter("@end",end) };
CREATE PROCEDURE [dbo].[pro_splitpage] ( @SQL NVARCHAR(4000), --要执行的SQL语句 @PAGE INT = 1, --要显示的页码 @PAGESIZE INT, --每页的大小 @PAGECOUNT INT = 0 OUT, --总页数 @RECORDCOUNT INT = 0 OUT --总记录数 ) AS BEGIN SET NOCOUNT ON DECLARE @P1 INT EXEC SP_CURSOROPEN @P1 OUTPUT, @SQL, @SCROLLOPT = 1, @CCOPT = 1, @ROWCOUNT = @PAGECOUNT OUTPUT SET @RECORDCOUNT = @PAGECOUNT SELECT @PAGECOUNT= CEILING(1.0 * @PAGECOUNT / @PAGESIZE) , @PAGE = (@PAGE-1) * @PAGESIZE + 1 EXEC SP_CURSORFETCH @P1, 16, @PAGE, @PAGESIZE EXEC SP_CURSORCLOSE @P1 END -------------------------- 调用 --------------------------- create proc [pro_select_a] ( @name VARCHAR(20)='', @pPageSize int= 10, -- 每页记录数 @pPageIndex int= 1, -- 当前页码 @outvalue int= 0 OUTPUT -- 总记录数 ) as BEGIN declare @sql varchar(4000) set @sql=' SELECT *from a where 1=1 ' IF isnull(@name,'')<>'' BEGIN set @sql=@sql+' and name='''+@name+'''' ENDEXEC pro_splitpage @sql,@pPageIndex ,@pPageSize,@PageCount, @outvalue OUTPUT END
CREATE PROCEDURE [dbo].[pro_splitpage] ( @SQL NVARCHAR(4000), --要执行的SQL语句 @PAGE INT = 1, --要显示的页码 @PAGESIZE INT, --每页的大小 @PAGECOUNT INT = 0 OUT, --总页数 @RECORDCOUNT INT = 0 OUT --总记录数 ) AS BEGIN SET NOCOUNT ON DECLARE @P1 INT EXEC SP_CURSOROPEN @P1 OUTPUT, @SQL, @SCROLLOPT = 1, @CCOPT = 1, @ROWCOUNT = @PAGECOUNT OUTPUT SET @RECORDCOUNT = @PAGECOUNT SELECT @PAGECOUNT= CEILING(1.0 * @PAGECOUNT / @PAGESIZE) , @PAGE = (@PAGE-1) * @PAGESIZE + 1 EXEC SP_CURSORFETCH @P1, 16, @PAGE, @PAGESIZE EXEC SP_CURSORCLOSE @P1 END -------------------------------- 调用 --------------------------------- CREATE PROC [dbo].[proc_select_sys_project] ( @regionid NVARCHAR(50)='', ---区域 @status NVARCHAR(50)='', --状态 @name NVARCHAR(50)='', --用户名 @saletype INT=1, --销售类型(1公司项目,0代理商项目) @startdate NVARCHAR(50)='', --启动日期开始时间 @startdate2 NVARCHAR(50)='', --启动日期结束时间 @enddate NVARCHAR(50)='', --完成日期开始时间 @enddate2 NVARCHAR(50)='', --完成日期结束时间 @SignDate NVARCHAR(50)='', --签收日期开始时间 @SignDate2 NVARCHAR(50)='', --签收日期结束时间 -- @vendorid NVARCHAR(50)='', --代理商 @pPageSize int= 10, -- 每页记录数 @pPageIndex int= 1, -- 当前页码 @outvalue int= 0 OUTPUT -- 总记录数 ) AS BEGIN declare @sql varchar(5000)--------根据条件查询 DECLARE @PageCount INT -----总页数 set @sql='select *from view_sys_project where Sale_Type=CONVERT(INT,' + STR( @saletype)+')' IF ISNULL(@regionid,'')<>'0' BEGIN SET @sql=@sql+' and Region_ID = CONVERT(INT,'+ @regionid+')' ENDIF ISNULL(@vendorid,'')<>'0' BEGIN SET @sql=@sql+' and Vendor_ID = CONVERT(INT,'+ @vendorid+')' ENDIF ISNULL(@status,'')<>'0' BEGIN SET @sql=@sql+' and [Status] = CONVERT(INT,'+ @status +')' ENDIF ISNULL(@name,'')<>'' BEGIN SET @sql=@sql+' and Customer_Name like''%'+@name+'%''' ENDIF ISNULL(@startdate,'')<>'' AND ISNULL(@startdate2,'')<>'' BEGIN SET @sql=@sql+' and CONVERT(datetime,Start_Date) BETWEEN '''+@startdate+''' and '''+ @startdate2+'''' ENDIF ISNULL(@enddate,'')<>'' AND ISNULL(@enddate2,'')<>'' BEGIN SET @sql=@sql+' and CONVERT(datetime, End_Date ) BETWEEN'''+@enddate+''' and '''+ @enddate2+'''' END IF ISNULL(@SignDate,'')<>'' AND ISNULL(@SignDate2,'')<>'' BEGIN SET @sql=@sql+' and CONVERT(datetime, Sign_Date) BETWEEN'''+@SignDate+''' and '''+ @SignDate2+'''' ENDEXEC pro_splitpage @sql,@pPageIndex ,@pPageSize,@PageCount, @outvalue OUTPUTEND
CREATE PROCEDURE Page @PageIndex INT, @PageSize INT, @tableName varchar(30), ---表名称 @colNames varchar(50), --查询表的列 @orderby varchar(50), --排序规则 @oredrType varchar(10) = 'ASC' --排序是降序,还是升序 AS DECLARE @StartData INT DECLARE @EndData INT SET @StartData = (@PageIndex-1)*@PageSize+1 SET @EndData=@PageSize*@PageIndexDECLARE @strSQL VARCHAR(1000) SET @strSQL='SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY '+@orderby+' '+@oredrType+') AS A,'+@colNames+' FROM '+@tableName+') AS P WHERE A BETWEEN '+STR(@StartData)+' AND '+STR(@EndData)+'' PRINT (@strSQL) EXEC (@strSQL)
我的意思是要存储过程 传参传完整SQL语句
ALTER PROCEDURE GetDataList
(
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@TotalPage int output --返回总页数
)
as
begin
Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int;
--计算总记录数if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord intset @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
不要传表明 字段名 where的这种形式。
CSDN的人回答问题都不看别人要求什么
//当前页第一条记录对应的行号
int start = (pageIndex - 1) * pageSize + 1;
//当前页最后一条记录对应的行号
int end = start + pageSize - 1; string sql = "select * from " +
"(select row_number() over(order by id) as n,* from Books) N_Books " +
"where n between @start and @end"; List<Book> list = new List<Book>(); try
{
SqlParameter[] par = new SqlParameter[]
{
new SqlParameter("@start",start),
new SqlParameter("@end",end)
};
(
@SQL NVARCHAR(4000), --要执行的SQL语句
@PAGE INT = 1, --要显示的页码
@PAGESIZE INT, --每页的大小
@PAGECOUNT INT = 0 OUT, --总页数
@RECORDCOUNT INT = 0 OUT --总记录数
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @P1 INT
EXEC SP_CURSOROPEN @P1 OUTPUT, @SQL, @SCROLLOPT = 1, @CCOPT = 1, @ROWCOUNT = @PAGECOUNT OUTPUT
SET @RECORDCOUNT = @PAGECOUNT
SELECT @PAGECOUNT=
CEILING(1.0 * @PAGECOUNT / @PAGESIZE) , @PAGE = (@PAGE-1) * @PAGESIZE + 1
EXEC SP_CURSORFETCH @P1, 16, @PAGE, @PAGESIZE
EXEC SP_CURSORCLOSE @P1
END
--------------------------
调用
---------------------------
create proc [pro_select_a]
(
@name VARCHAR(20)='',
@pPageSize int= 10, -- 每页记录数
@pPageIndex int= 1, -- 当前页码
@outvalue int= 0 OUTPUT -- 总记录数
)
as
BEGIN
declare @sql varchar(4000)
set @sql=' SELECT *from a where 1=1 ' IF isnull(@name,'')<>''
BEGIN
set @sql=@sql+' and name='''+@name+''''
ENDEXEC pro_splitpage @sql,@pPageIndex ,@pPageSize,@PageCount, @outvalue OUTPUT
END
(
@SQL NVARCHAR(4000), --要执行的SQL语句
@PAGE INT = 1, --要显示的页码
@PAGESIZE INT, --每页的大小
@PAGECOUNT INT = 0 OUT, --总页数
@RECORDCOUNT INT = 0 OUT --总记录数
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @P1 INT
EXEC SP_CURSOROPEN @P1 OUTPUT, @SQL, @SCROLLOPT = 1, @CCOPT = 1, @ROWCOUNT = @PAGECOUNT OUTPUT
SET @RECORDCOUNT = @PAGECOUNT
SELECT @PAGECOUNT=
CEILING(1.0 * @PAGECOUNT / @PAGESIZE) , @PAGE = (@PAGE-1) * @PAGESIZE + 1
EXEC SP_CURSORFETCH @P1, 16, @PAGE, @PAGESIZE
EXEC SP_CURSORCLOSE @P1
END --------------------------------
调用
---------------------------------
CREATE PROC [dbo].[proc_select_sys_project]
(
@regionid NVARCHAR(50)='', ---区域
@status NVARCHAR(50)='', --状态
@name NVARCHAR(50)='', --用户名
@saletype INT=1, --销售类型(1公司项目,0代理商项目)
@startdate NVARCHAR(50)='', --启动日期开始时间
@startdate2 NVARCHAR(50)='', --启动日期结束时间
@enddate NVARCHAR(50)='', --完成日期开始时间
@enddate2 NVARCHAR(50)='', --完成日期结束时间
@SignDate NVARCHAR(50)='', --签收日期开始时间
@SignDate2 NVARCHAR(50)='', --签收日期结束时间 --
@vendorid NVARCHAR(50)='', --代理商
@pPageSize int= 10, -- 每页记录数
@pPageIndex int= 1, -- 当前页码
@outvalue int= 0 OUTPUT -- 总记录数
)
AS
BEGIN
declare @sql varchar(5000)--------根据条件查询
DECLARE @PageCount INT -----总页数
set @sql='select *from view_sys_project
where Sale_Type=CONVERT(INT,' + STR( @saletype)+')'
IF ISNULL(@regionid,'')<>'0'
BEGIN
SET @sql=@sql+' and Region_ID = CONVERT(INT,'+ @regionid+')'
ENDIF ISNULL(@vendorid,'')<>'0'
BEGIN
SET @sql=@sql+' and Vendor_ID = CONVERT(INT,'+ @vendorid+')'
ENDIF ISNULL(@status,'')<>'0'
BEGIN
SET @sql=@sql+' and [Status] = CONVERT(INT,'+ @status +')'
ENDIF ISNULL(@name,'')<>''
BEGIN
SET @sql=@sql+' and Customer_Name like''%'+@name+'%'''
ENDIF ISNULL(@startdate,'')<>'' AND ISNULL(@startdate2,'')<>''
BEGIN
SET @sql=@sql+' and CONVERT(datetime,Start_Date) BETWEEN '''+@startdate+''' and '''+ @startdate2+''''
ENDIF ISNULL(@enddate,'')<>'' AND ISNULL(@enddate2,'')<>''
BEGIN
SET @sql=@sql+' and CONVERT(datetime, End_Date ) BETWEEN'''+@enddate+''' and '''+ @enddate2+''''
END
IF ISNULL(@SignDate,'')<>'' AND ISNULL(@SignDate2,'')<>''
BEGIN
SET @sql=@sql+' and CONVERT(datetime, Sign_Date) BETWEEN'''+@SignDate+''' and '''+ @SignDate2+''''
ENDEXEC pro_splitpage @sql,@pPageIndex ,@pPageSize,@PageCount, @outvalue OUTPUTEND
CREATE PROCEDURE Page
@PageIndex INT,
@PageSize INT,
@tableName varchar(30), ---表名称
@colNames varchar(50), --查询表的列
@orderby varchar(50), --排序规则
@oredrType varchar(10) = 'ASC' --排序是降序,还是升序
AS
DECLARE @StartData INT
DECLARE @EndData INT
SET @StartData = (@PageIndex-1)*@PageSize+1
SET @EndData=@PageSize*@PageIndexDECLARE @strSQL VARCHAR(1000)
SET @strSQL='SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY '+@orderby+' '+@oredrType+') AS A,'+@colNames+' FROM '+@tableName+') AS P WHERE A
BETWEEN '+STR(@StartData)+' AND '+STR(@EndData)+''
PRINT (@strSQL)
EXEC (@strSQL)