存儲(chǔ)過(guò)程中如何執(zhí)行帶輸出參數(shù)的動(dòng)態(tài)SQL

SQL Server存儲(chǔ)過(guò)程中執(zhí)行帶輸出參數(shù)的動(dòng)態(tài)sql是很多人經(jīng)常碰到的問(wèn)題，比如根據(jù)一些條件查詢列表，并返回記錄數(shù)等。下面是一個(gè)參考示例，查詢用戶列表，它可以利用臨時(shí)表實(shí)現(xiàn)翻頁(yè)，并帶有死鎖和超時(shí)檢測(cè)功能。
    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) --聲明動(dòng)態(tài)sql執(zhí)行語(yǔ)句
    declare @pagecount int --當(dāng)前頁(yè)數(shù)
    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 '
    --取得當(dāng)前數(shù)據(jù)庫(kù)的記錄總數(shù)
    declare @row_num int
    LockTimeOutRetry:
    --創(chuàng)建臨時(shí)表，作為數(shù)據(jù)過(guò)濾
    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