SQL Server存储过程中执行带输出参数的动态sql是很多人经常碰到的问题比如根据一些条件查询列表并返回记录数等下面是一个参考示例查询用户列表它可以利用临时表实现翻页并带有死锁和超时检测功能
CREATE procedure pUserList
(
@UserType char()
@pagenum int
@perpagesize int
@pagetotal int out
@rowcount int out
)
as
set nocount on
DECLARE @Err INT@ErrCounter INT
declare @sql nvarchar() 声明动态sql执行语句
declare @pagecount int 当前页数
declare @sWhere nvarchar()
declare @sOrder nvarchar()
set @sWhere = where =
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 @sqlN@row_num int output @row_num output
if @row_num % @perpagesize =
set @pagetotal = @row_num/@perpagesize
else
set @pagetotal = @row_num/@perpagesize +
set @rowcount = @row_num
if @row_num > @perpagesize
begin
set @row_num = @pagenum * @perpagesize
if @row_num = @perpagesize
begin
set @sql = Nselect top + cast(@perpagesize as varchar)
+ UserIDLoginNameRealName from dbo[User] + @sWhere + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> GOTO ErrorHandler
return
end
else
begin
set @row_num = (@pagenum) * @perpagesize
set @pagecount = @row_num
set @sql=Ninsert #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 = Nselect top + cast(@perpagesize as varchar) + UserIDLoginNameRealName from dbo[User] +@sWhere+ and UserID not in (select T_id from #change) + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> GOTO ErrorHandler
return
end
end
else
begin
set @sql = select UserIDLoginNameRealName
from dbo[User] + @sWhere + @sOrder
exec sp_executesql @sql
SET @Err = @@ERROR
IF @Err <> GOTO ErrorHandler
return
end
ErrorHandler:
IF (@Err = OR @Err = ) AND @ErrCounter =
BEGIN
RAISERROR (Unable to Lock Data after five attempts )
return
END
IF @Err = OR @Err = Lock Timeout / Deadlock
BEGIN
WAITFOR DELAY ::
SET @ErrCounter = @ErrCounter +
GOTO LockTimeOutRetry
END
else unknown error
RAISERROR (@err ) WITH LOG
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
