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') 

參考來源

這個網誌中的熱門文章

[TFS] 分支與合併