# [112]天方科技 ASP.net core 教育訓練 1120502

### 將查詢欄位串成`json`字串輸出

Create or ALTER   procedure [dbo].[up_jsonCmparea] @json nvarchar(max) output
as
    set nocount on;
    select @json='['+ 
    STRING_AGG(
    concat('{"cmp_area":', cmp_area,',"cmp_name_abr":"', cmp_name_abr, '","cmp_name_full":"', cmp_name_full,'"}')
    , ',') 
    +']'
    from s90_cmparea

    go

    declare @x nvarchar(max)
    exec up_jsonCmparea @x output
    select @x

### 建立一個`Get`方法呼叫`up_jsonCmparea()`

 [HttpGet("sp/sp/json")]
public async Task<ContentResult> up_jsonCmparea()   //ContentResult根據資料內容自動處理
{

    //var parameterreturnValue = new SqlParameter("returnValue", SqlDbType.Int)
    //{
    //    Direction = ParameterDirection.Output,
    //};

    var sqlParameters = new[]
    {
        new SqlParameter("@json", SqlDbType.NVarChar,-1)    //-1 = Max
        {
            Direction= ParameterDirection.Output,
        },
        //parameterreturnValue,
    };
    var result = await _context.Database.ExecuteSqlRawAsync("EXEC [dbo].[up_jsonCmparea] @json Output", sqlParameters);

    string json = sqlParameters[0].Value.ToString() ?? "[]";


    return Content(json, "application/json", Encoding.UTF8);

}