在存储过程输入输出参数中使用json

Sql Server 从 2016 开始支持 json 操作，JSON支持适用于 SqlServer 2016 及以上版本 和 Azure SQL Database。sqlserver文档参考这里。

有了这个json支持，默认过程只传入一个json字符串参数就可以了，方便很多。我们可以在内部做很多事情。甚至可以校验参数，鉴权或将结果集封装为标准的前后端json({code,msg,data})格式。这样等于是前端直接进校了透传，业务变化，中间层也不用改动。

JSON 相关的方法：
判断一段字符串是否是标准的 json（ISJSON）
直接查询数据成 json 格式(FOR JSON PATH) ，类似于之前的查询一个 xml (FOR XML PATH)，
查询一个 json 对象的值(JSON_VALUE)
查询一个 json 数组值JSON_QUERY
更新一段JSON的内容，修改 JSON 对象里的属性值，删除 JSON 对象里的某一个属性，增加属性，JSON_MODIFY
解析一段 json 内容 (OPENJSON)

 

定义一个简单的过程：

ALTER PROCEDURE [dbo].[SPABC] (  @Param VARCHAR(4000))
AS
BEGIN

    SELECT json_value(@Param,'$.NAME')

END

参数Param是json格式字符串，作为入参，在过程内被解析使用。

 

navicat客户端执行过程

 或

 区别在于数据格式不一样。

 

修改过程，查询结果集并转为json输出

ALTER PROCEDURE [dbo].[SPABC] (  @Param VARCHAR(4000))
AS
BEGIN
    -- SELECT json_value(@Param,'$.NAME')

      DECLARE @Name NVARCHAR(MAX) --定义一个内部变量接收json内部字段数据
      set @Name=json_value(@Parms,'$.NAME') --设置值

    -- 。。。内部使用变量。。。
　　-- select @Name as name 
    SELECT * from Sys_Users for json auto --此处不能使用WITHOUT_ARRAY_WRAPPER，否则代码里转换出错，格式不对
END


C#代码：

//注意，此处res有可能为多行json字符串格式，需要连接并转json对象
var res = execSPDAO.ExecUserSpDataTable(spName, param);
StringBuilder stringBuilder = new StringBuilder();
foreach(DataRow dr in res.Rows)
{
stringBuilder.Append(dr[0]);
}
var obj = JsonConvert.DeserializeObject(stringBuilder.ToString());
return new { code = 0, msg = "查询成功", data = obj };