set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetDataSet]
@TableList Varchar(200)=‘*‘,
@TableName Varchar(300),
@SelectWhere Varchar(1000)=‘‘,
@SelectOrderId Varchar(200),
@SelectOrder Varchar(200)=‘‘,
@intPageNo int=1, –页号
@intPageSize int=10 –每页显示数
as
begin
set nocount on–关闭计数
declare @RecordCount int –总记录数(存储过程输出参数)
declare @TmpSelect NVarchar(2000)
declare @Tmp NVarchar(600)
if @SelectWhere=‘‘
begin
set @TmpSelect = ‘select @RecordCount = count(*) from ‘+@TableName
end
else
begin
set @TmpSelect = ‘select @RecordCount = count(*) from ‘+@TableName +‘ where ‘+@SelectWhere
end
execute sp_executesql @TmpSelect, –执行上面的sql语句
N‘@RecordCount int OUTPUT‘ , –执行输出数据的sql语句，output出总记录数
@RecordCount OUTPUT
–if (@RecordCount = 0) –如果没有，则返回零
–return 0
/*判断页数是否正确*/
if (@intPageNo – 1) * @intPageSize > @RecordCount –页号大于总页数，返回错误
return (-1)
–set nocount off–打开计数
if @SelectWhere != ‘‘
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+‘ ‘+@TableList+‘ , ‘‘‘+str(@RecordCount)+‘‘‘ as Count from ‘+@TableName+‘ where ‘+@SelectOrderId+‘ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+‘ ‘+@SelectOrderId+‘ from ‘+@TableName+‘ where 1=1 and ‘+@SelectWhere +‘ ‘+@SelectOrder+‘) and ‘+@SelectWhere +‘ ‘+@SelectOrder
end
else
begin
set @TmpSelect = ‘select top ‘+str(@intPageSize)+‘ ‘+@TableList+‘ , ‘‘‘+str(@RecordCount)+‘‘‘ as Count from ‘+@TableName+‘ where ‘+@SelectOrderId+‘ not in(select top ‘+str((@intPageNo-1)*@intPageSize)+‘ ‘+@SelectOrderId+‘ from ‘+@TableName+‘ ‘+@SelectOrder+‘) ‘+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)
end