SQL Server傳遞資料集到預存程序
今天同事提到他將資料集用參數的方式丟到預存程序裡面，大致的作法如下:
1.資料庫宣告資料表型別
CREATE TYPE dbo.USER_TABLE_TYPE AS TABLE (
USER_NO VARCHAR(8)
)
2.C#宣告一個DataTable及參數
var table = new DataTable();
table.Columns.Add(new DataColumn("USER_NO", System.Type.GetType("System.String")));
table.Rows.Add(table.NewRow());
table.Rows[0]["USER_NO"] = "Abc";
var par = new SqlParameter
{
ParameterName = "userTable",
Value = table,
SqlDbType = SqlDbType.Structured,
TypeName = "dbo.USER_TABLE_TYPE"
};
3.預存程序參數宣告
CREATE PROCEDURE dbo.GetUsers
@userTable USER_TABLE_TYPE READONLY
AS
BEGIN
SELECT * FROM @userTable
END
使用JSON
若嫌上面的作法比較麻煩的話，其實可以考慮使用JSON的方式傳入，不過缺點是不支援強型別及效能會慢一點。以下為個人測試JSON在SQL的一些操作
物件陣列查詢
--宣告Object Array
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{ "USER_NO" : "A0000000" },
{ "USER_NO" : "B0000001" }
]'
--Join的方式查詢
SELECT U.* FROM (
SELECT * FROM OPENJSON(@json) WITH (USER_NO varchar(8) ' $.USER_NO')
) AS J,USER U
WHERE J.USER_NO=U.USER_NO;
--In的方式查詢
SELECT * FROM USER U WHERE U.USER_NO IN (
SELECT USER_NO FROM OPENJSON(@json) WITH (USER_NO varchar(8) ' $.USER_NO')
)
字串陣列杳詢
SET @json = N'[ "A0000000","B0000001"]'
SELECT U.* FROM (
SELECT * FROM OPENJSON(@json) WITH (USER_NO varchar(8) ' $')
) AS J,USER U
WHERE J.USER_NO=U.USER_NO;
將現有的Table轉成Json
SELECT * FROM USER FOR JSON PATH
測試將資料表的Json 轉成Table
DECLARE @V AS NVARCHAR(MAX)
select @V=PVALUE from ROFILE WHERE USER_NO='A0000001' AND AREA='A90' AND CTRL='Home' AND PKEY='Recent'
select * from OPENJSON(@V)
WITH (PNO varchar(8) '$.PNO',Last_Utc datetime2 '$.Last_Utc')
參考來源
