sql-server – 存储过程OUT参数始终返回NULL

发布时间：2020-12-12 07:45:29 所属栏目：MsSql教程来源：网络整理

导读：我的存储过程返回预期值,但OUT参数不返回任何内容.在程序退出之前打印out参数的值,参数的值设置并且看起来很棒.那么为什么我的测试代码没有得到任何价值呢？ USE MyDBGOEXECUTE sp_addmessage @msgnum = 51001,@severity = 16,@msgtext = N'Resource NOT Avai

我的存储过程返回预期值,但OUT参数不返回任何内容.在程序退出之前打印out参数的值,参数的值设置并且看起来很棒.那么为什么我的测试代码没有得到任何价值呢？

USE MyDB
GO
EXECUTE sp_addmessage 
   @msgnum   = 51001,@severity = 16,@msgtext  = N'Resource NOT Available',@lang     = 'us_english',@replace  = REPLACE
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewFileNumber]') AND type in (N'P',N'PC'))
DROP PROCEDURE [dbo].[GetNewFileNumber]
GO


CREATE PROCEDURE dbo.GetNewFileNumber
   (
      @NextFileNum  nvarchar(11) = NULL output
   )
AS
BEGIN

    set transaction isolation level serializable
    begin transaction

    declare @LockResult int;
    declare @CurrentDate date;
    declare @FileNumberDate date;
    declare @FileNumber int;

    execute @LockResult = sp_getapplock 
        @Resource    = 'GetNewFileNumber_TRANSACTION',@LockMode    = 'Exclusive',@LockTimeout = 0
    if @LockResult <> 0
    begin
        rollback transaction
        raiserror ( 51001,16,1 )
        return
    end


        set @CurrentDate = CONVERT (date,GETUTCDATE());

    select @FileNumberDate = filedate,@FileNumber = fileCount from  dbo._globalCounters;
    if @FileNumberDate != @CurrentDate
    begin
        set @FileNumberDate = @CurrentDate;
        set @FileNumber = 0;
    end

    set @FileNumber = @FileNumber + 1;
    update dbo._globalCounters
        set fileDate = @FileNumberDate,fileCount = @FileNumber;

    set @NextFileNum = convert(nvarchar(6),@FileNumberDate,12) + 
        '-' + 
        RIGHT('00'+convert(nvarchar(2),@FileNumber),2);

    execute sp_releaseapplock 'GetNewFileNumber_TRANSACTION'

    commit transaction
    print 'filenum:' + @NextFileNum     
    return @FileNumber;
END

GO

--Test the procedure
DECLARE @return_value int
DECLARE @out_value nvarchar(11)
EXEC    @return_value = [dbo].[GetNewFileNumber] @out_value 
SELECT  'Return Value' = @return_value,'Out Value' = @out_value -- Out value always     returns null?!
GO

解决方法

调用它时,需要将其标记为输出参数

EXEC    @return_value = [dbo].[GetNewFileNumber] @out_value OUTPUT

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!