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

發表于:2008-05-08來源:作者:點擊數: 標簽:數據庫sqlSQLSqlint
數據庫 基礎:存儲過程中如何執行帶輸出參數的動態SQL 作者:GOD 來源:希賽網 SQL Server存儲過程中執行帶輸出參數的動態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.anti-gravitydesign.com

国产97人人超碰caoprom_尤物国产在线一区手机播放_精品国产一区二区三_色天使久久综合给合久久97