Stored Procedure OUT parameter always returning NULL

Question

My stored procedure returns the expected value, but the OUT parameter is not returning anything. Printing the value of the out parameter just before the procedure exits, the parameter's value is set and looks great. So why is my test code not getting any value back?

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

Answer 1

You need to mark it as an output parameter when you call it

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