CREATE procedure pUserList
(
@UserType char(2),
@pagenum int,
@pERPagesize int,
@pagetotal int out,
@rowcount int out
)
as
set nocount on
DECLARE @Err INT,@ErrCounter INT
declare @sql nvarchar(2000) --声明动态sql执行语句
declare @pagecount int --当前页数
declare @sWhere nvarchar(200)
declare @sOrder nvarchar(100)
set @sWhere = " where 1=1 "
if not(@UserType is null)
set @sWhere = @sWhere + " and UserType = " + @UserType
set @sOrder = " order by UserID "
--取得当前数据库的记录总数
declare @row_num int
LockTimeOutRetry:
--创建临时表,作为数据过滤
create table #change (T_id int)
set @sql = "select @row_num=count(*) from dbo.[User]" + @sWhere
exec sp_executesql @sql,N"@row_num int output", @row_num output
if @row_num % @perpagesize =0
set @pagetotal = @row_num/@perpagesize
else
set @pagetotal = @row_num/@perpagesize + 1
set @rowcount = @row_num
if @row_num > @perpagesize
begin
set @row_num = @pagenum * @perpagesize
if @row_num = @perpagesize
begin
set @sql = N"select top " + cast(@perpagesize as varchar) + "
UserID,LoginName,RealName from dbo.[User]" + @sWhere + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> 0 GOTO ErrorHandler
return 0
end
else
begin
set @row_num = (@pagenum-1) * @perpagesize
set @pagecount = @row_num
set @sql=N"insert #change (T_id) select top " + cast(@pagecount as varchar)
+ " UserID from dbo.[User] "+@sWhere+" and UserID
not in (select T_id from #change)" + @sOrder
exec sp_executesql @sql
set @sql = N"select top " + cast(@perpagesize as varchar) + "
UserID,LoginName,RealName from dbo.[User] "+@sWhere+"
and UserID not in (select T_id from #change)" + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> 0 GOTO ErrorHandler
return 0
end
end
else
begin
set @sql = "select UserID,LoginName,RealName
from dbo.[User]" + @sWhere + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> 0 GOTO ErrorHandler
return 0
end
ErrorHandler:
IF (@Err = 1222 OR @Err = 1205) AND @ErrCounter = 5
BEGIN
RAISERROR ("Unable to Lock Data after five attempts.", 16,1)
return -100
END
IF @Err = 1222 OR @Err = 1205 -- Lock Timeout / Deadlock
BEGIN
WAITFOR DELAY "00:00:00.25"
SET @ErrCounter = @ErrCounter + 1
GOTO LockTimeOutRetry
END
-- else unknown error
RAISERROR (@err, 16,1) WITH LOG
return -100
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO |