以前一直用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的支持同時帶來的還有表結構的設計變化，原來要主表明細表的，現在很多場景可以一張表搞定。
