數據庫基礎：存儲過程中如何執行帶輸出參數的動態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.kjueaiud.com