这是个经常碰到的问题,比如根据一些条件查询列表,并返回记录数等。下面是个例子,查询用户列表,用临时表实现翻页,并带有死锁和超时检测功能。
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
(
@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