熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

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

2013-11-13 12:37:37  來源: 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.