调试存储过程老是提示“Invalid column name ...”，晕倒，检查到最后发现是字符串竟然写成了""，哈哈，应该是 ''，记住了。
在这里整理了两个可以用的分页存储过程，以备后用。
第一：ALTER procedure Consignment@tablename varchar(80) ,@strOrder varchar(50) ,@PageIndex int = 1,@PageSize int = 15,@strGetFields varchar(200) = '*',@OutPut int outputas BeginDeclare @strSql varchar(500)DECLARE @SQL NVARCHAR(1000)DECLARE @R BIGINTSET @SQL= N'select @R=count(*) from '+@TableNameEXEC SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUTSET @OutPut= @Rif(@PageIndex =1)Begin set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' order by '+@strOrderEndElse set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder +' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by ' +@strOrder+' ) as tempTable)) order by '+ @strOrder select @strSql exec(@strSql)End
第二：
alter procedure AllProce@tablename varchar(200) , --表名@strGetFields varchar(200) = '*', --查询列名@PageIndex int = 1 , --页码@pageSize int = 15, --页面大小@strWhere varchar(100) = '', --查询条件@strOrder varchar(100) = '', --排序列名@intOrder bit = 0, --排序类型 1为升序@CountAll bigint output --返回纪录总数用于计算页面数 asbegindeclare @strSql varchar(500) --主语句declare @strTemp varchar(100) --临时变量declare @strOrders varchar(50) --排序语句declare @table varchar(70)declare @SQL nvarchar(1000)declare @R bigintset @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName)exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUTset @CountAll= @Rif @intOrder = 0begin --为0是升序 set @strTemp = '>(select max' set @strOrders = ' order by '+@strOrder+' asc 'endelsebegin --否则为降序 set @strTemp = '<(select min' set @strOrders = ' order by '+@strOrder+' desc 'endif @PageIndex =1 --第一页直接读出纪录begin if @strWhere = '' begin set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders end else begin set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders endendelsebegin set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')' +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders if @strWhere != ' ' begin set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') ' +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders endend exec(@strSql) endGO
