Stored Procedure OUT parameter always returning NULL

sql-server tsql stored-procedures

16,246

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

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

16,246

Author by

Constablebrew

I’m a software developer with over 17 years of experience in both front end and back end development and database management. I have worked on many different applications on a wide variety of platforms, including Node.js, React, AngularJS, ASP.NET, SQL Server, Postgres, MongoDB, and Access, and with a variety of languages including JavaScript, C#, and VB. Most recently, I was a Senior Software Engineer at Automation Anywhere, focusing on building React interfaces for our top notch process automation bots. Prior to that I served as Full-stack developer at Mobiquity, building health care applications with a MEAN stack. I have also been fortunate to have worked with multiple other technologies and platforms, including AngularJS, BackboneJS, JQuery, PHP Zend, and both ASP and C# .NET. Despite recent years focusing on client side development, I have built some sweet scripts and queries in SQL Server, Postgres, MongoDB, and even MS Access. Colleagues know me as a highly creative problem solver who works hard to get things done. I am often eager to hit the ground running, as I tend to find the best questions to ask once I have put in some time thinking about the possible solutions. I work well alone, but I’m at my best when collaborating with others.

Updated on June 08, 2022

Comments

Constablebrew about 2 years

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

julealgon over 5 years

My god this is so incredibly unintuitive! I just spend half an hour debugging my proc to see why the output was always null when it was supposed to be 0.