以前一直用sql server2008，自从升级成sql server2019后，用OPENJSON的感觉像开挂，想想以前表作为参数传输时的痛苦，不堪回首。
一》不堪回首
为了执行效率，很多时候希望将表作为参数传给数据库的存储过程。存储过程支持自定义表，只是很麻烦。
1.需要自定义一个表类型
create type paratable as table
(Pname varchar(20),Pcode varchar(20),id int)
2.创建存储过程，参数用自己定义的表类型
create procedure Procedurename
@Paratable paratable
as
begin
insert into Mtable (Pname,Pcode) select Pname,Pcode from @Paratable
end
如果你的参数结构变量，又需要去修改自定义的表类型，很麻烦。
3.在程序端
SqlConnection myconn = new SqlConnection(connectionString);//链接数据库字符串
SqlCommand mycmd = myconn .CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Procedurename";
SqlParameter p = cmd.Parameters.AddWithValue("@Paratable", paratable);
二》开挂的OPENJSON的威力
有了sql server对Json的支持，状况改变了。
语法：OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]
第一个参数是你的json字符串
第二个参数是json中你要取的部分的路径
第三个参数指定字段名称<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
假设1：要传的Json字符为作为varchar(max)类型传输，
[
{
"name": "John",
"code":"T003"
},
{
"name": "Jane",
"code":"T004"
}
]
最简单的方式，Json里面只有一层：
不指定字段名称
select * from openjson(@pjson)
指定字段名称
select * from openjson(@pjson)
with (
name varchar(20) '$.name',
code varchar(20) '$.code'
)
不过我习惯给起个名，无名感觉不习惯。
{MyTable:
[
{
"name": "John",
"code":"T003"
},
{
"name": "Jane",
"code":"T004"
}
]
}
select * from openjson(@pjson,'$.MyTable')
with (
name varchar(20) '$.name',
code varchar(20) '$.code'
)
假设2：复杂一点，Json里面有层级
DECLARE @json NVARCHAR(MAX) = N'[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]'
SELECT * FROM OPENJSON (@json)
WITH (
Number VARCHAR(200) '$.Order.Number',
Date DATETIME '$.Order.Date',
Customer VARCHAR(200) '$.AccountNumber',
Quantity INT '$.Item.Quantity',
[Order] NVARCHAR(MAX) AS JSON
)
注：还附带了一个 $.sql:identity() 来指定标识列
Json的支持同时带来的还有表结构的设计变化，原来要主表明细表的，现在很多场景可以一张表搞定。