存储过程中执行带输出参数的动态"SQL"是很多人经常碰到的问题，比如根据一些条件查询列表，并返回记录数等。下文中提供一个具体的示例，查询用户列表，用临时表实现翻页，并带有死锁和超时检测功能。
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
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
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
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
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
