Scalar SQL function not returning correct JSON values

Question

I need JSON returned from a SQL Server scalar function.

I wrote this query inside SSMS

SELECT 
    Id AS [Expense.Id],
    Amount AS [Expense.Amount],
    StudentName AS [Expense.StudentName]
FROM 
    [dbo].[Expenses] 
FOR JSON PATH, Root('Expenses')

Here is result (EXACTLY WHAT I WANTED)

"Expenses": [
    {
      "Expense": {
        "Id": "43838475-5BBC-4C2A-A1E9-85F9E55298D3",
        "Amount": 0.0000000e+000,
        "StudentName": "Carter"
      }
    },
    {
      "Expense": {
        "Id": "38AC0403-5DCF-42E1-A103-5D33DB905F57",
        "Amount": 0.0000000e+000,
        "StudentName": "Louis"
      }
    },
    {
      "Expense": {
        "Id": "5E6EB81E-C988-4D7C-98AB-FA814CFF875A",
        "Amount": 0.0000000e+000,
        "StudentName": "David"
      }
    }

Now then all I have to do is wrap it in a function:

CREATE FUNCTION [dbo].[get_all_json] 
    (@input nvarchar)
RETURNS nvarchar
AS
BEGIN
    DECLARE @return nvarchar

    SET @return = (SELECT 
                       Id AS [Expense.Id],
                       Amount AS [Expense.Amount],
                       StudentName AS [Expense.StudentName]
                   FROM 
                       [dbo].[Expenses] 
                   FOR JSON PATH, Root('Expenses'))
    RETURN @return
END

Now I call it in SSMS:

SELECT TRIM( dbo.get_all_json('1')) json_fm_func;

And I get this result back:

SELECT TRIM( dbo.get_all_json('1')) json_fm_func;

What went wrong?

Always specify a length when defining a varchar or nvarchar ... returns nvarchar(max), Be sure to fix @return as well — John Cappelletti
– John Cappelletti, Commented Mar 23, 2020 at 22:02
If you don't specify an explicit length on a VARCHAR or NVARCHAR parameter for a function or stored procedure, it defaults to a length of 1 character .... - works as designed (and documented) — marc_s
– marc_s, Commented Mar 24, 2020 at 5:49

Behnam · Accepted Answer · 2020-03-24 00:42:26Z

1

You need to change nvarchar to nvarchar(max)

create function [dbo].[get_all_json] (@input nvarchar(max)) 
--the argument (@input) is never used. you can create your function without argument. 
returns nvarchar(max) as
begin
    declare @return nvarchar(max)
    set  @return =  (SELECT Id as [Expense.Id]
      ,Amount As [Expense.Amount]
      ,StudentName As [Expense.StudentName]
          FROM [dbo].[Expenses] FOR JSON PATH, Root('Expenses'))
    return @return
end

answered Mar 24, 2020 at 0:42

Behnam

1,0833 gold badges14 silver badges39 bronze badges

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

1 Comment

Mark Wardell Over a year ago

Hi 5 @Behnam! Have a great evening

Collectives™ on Stack Overflow

Scalar SQL function not returning correct JSON values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related