title: Stored Procedure OUT parameter always returning NULL
body: <p>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?</p>
  
  <pre><code>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 &lt;&gt; 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
  </code></pre>