Stored Procedure Output variable returning null when assigning value from itself [duplicate]

Question

While trying OUTPUT variable, and assigning the variable with itself my proc returns null. The value is correctly assigning in the OUTPUT variable and works if I don't provide the optional output. Please check the below code

ALTER PROC [DBO].TEST (
    @IN NVARCHAR(10) ,
    @OUT NVARCHAR(MAX) = 'JAMES' OUTPUT)
AS
BEGIN
    SET @OUT = @OUT + '-' + @IN
    PRINT @OUT
END

---This Works
EXEC TEST 'BOND'
--OUTPUT : JASMES-BOND

---This doesn't Work
DECLARE @OUT1 NVARCHAR(MAX)
EXEC TEST 'BOND', @OUT1 OUTPUT
PRINT @OUT1
-- No Error, No Output

If I change the statement SET @OUT = @OUT + '-' + @IN to SET @OUT = '-' + @IN the code gives output, but that's not my desired output. Please help me

or perhaps Using Optional parameters when incoming value is null? — underscore_d
– underscore_d, Commented Jun 18, 2020 at 9:55

Dale K · Accepted Answer · 2020-06-18 09:54:40Z

0

I suspect there are two factors that are leading to this situation.

Thats how concatenation works in SQL Server, NULL + anything = NULL. So you need to handle the NULL case.
And the default value you provide only works when you don't provide a parameter, as shown by your first example. But its ignored when you provide a parameter, even when its NULL, as shown by your second example.

For completeness I recommend handling both @OUT and @IN e.g.

SET @OUT = COALESCE(@OUT,'') + '-' + COALESCE(@IN,'');

Note: You can of course put something other than an empty string in for the NULL case.

edited Jun 18, 2020 at 9:54

answered Jun 18, 2020 at 9:49

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored Procedure Output variable returning null when assigning value from itself [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related