Getting error in accepting nested JSON from stored procedure in ASP.NET Core Web API?-CodePudding

I am using Entity Framework to call a stored procedure. My stored procedure is creating JSON and I want to pass that JSON to Web API in the form of a string.

My stored procedure is:

CREATE PROCEDURE [dbo].[CreateJson]
    @AppointmentID int,
    @JsonOutput nvarchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @JsonOutput = (SELECT TOP 1 AppointmentID, ReturnCode
                       FROM appointments
                       WHERE appointmentID = @AppointmentID
                       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    RETURN;
END

API entity class which accept string:

public partial class sp_test
{
    public string jsonOutput { get; set; }
}

API controller:

[HttpGet]
public async Task<ActionResult<IEnumerable<sp_test>>> Getsp_test(int AppointmentID)
{
    string StoredJson = "exec CreateJson "  
                        "@AppointmentID = "   AppointmentID;

    return await _context.sp_test.FromSqlRaw(StoredJson).ToListAsync();
}

Error:

Can I get some guidance on what I am doing wrong? Thank you

CodePudding user response：

Declare @getJson nvarchar(max);
EXEC CreateJson
    @AppointmentIDr =  AppointmentID,
    @JsonOutput = @getJson OUTPUT

CodePudding user response：

If you want to get a result set compatible with the sp_test entity so that result set is consumable by FromSqlRaw

CREATE PROCEDURE [dbo].[CreateJson]
    @AppointmentID int
AS
BEGIN
    SET NOCOUNT ON;
    select (
       SELECT TOP 1 AppointmentID, ReturnCode 
       FROM appointments
       WHERE appointmentID = @AppointmentID
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) jsonOutput;
    RETURN;
END