SQL Server pass parameter to Procedure
Today, My colleague mention his work which how to pass the parameter to the procedure. The workaround as below:
1.Declare a custom table type
CREATE TYPE dbo.USER_TABLE_TYPE AS TABLE (
USER_NO VARCHAR(8)
)
2.C# Declare DataTable and SqlParameter
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.Declare parameter which type is just created
CREATE PROCEDURE dbo.GetUsers
@userTable USER_TABLE_TYPE READONLY
AS
BEGIN
SELECT * FROM @userTable
END
Use JSON
Maybe you feel the above way is too many steps, you may also consider use JSON to pass it. But, its defect is not supporting strong type and a bit slowly performance. The following samples are just my testing about JSON manipulate in SQL.
Object of Array query
--宣告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')
)
String of Array Query
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;
Convert table to Json
SELECT * FROM USER FOR JSON PATH
Turn a field value into a 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')
