數據庫基礎：存儲過程中如何執行帶輸出參數的動態SQL

　　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

From:http://tw.wingwit.com/Article/program/SQL/201311/16271.html