数据库基础：存储过程中如何执行带输出参数的动态SQL

数据库基础：存储过程中如何执行带输出参数的动态SQL

作者：GOD　来源：希赛网　

　　SQL Server存储过程中执行带输出参数的动态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
　　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

原文转自：http://www.ltesting.net