SqlServer: 存储过程传表类型参数的三种方式：表值参数/XML/JSON

在使用过程中，传一个表（数据集）的需求是真实存在的，经常有人问。

于是，总结了以前零零散散的知识，汇集为本文，分享给读者。

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	cid INT PRIMARY KEY,
	num INT,
	d DATE	   --SqlServer2005需要改为datetime
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES(1,23,'2021-11-10'),(2,45,'2021-11-11');
------ 以上为测试及数据表

--------------- 方法1：使用表值类型参数 --------------- 
--1.1 创建表值类型
IF OBJECT_ID('Proc_TestUtp') IS NOT NULL
	DROP PROC Proc_TestUtp
GO
IF EXISTS(SELECT 1 FROM sys.types WHERE NAME='utp_t')
	DROP TYPE utp_t
GO
CREATE TYPE utp_t AS TABLE 
(
	cid INT,
	num INT,
	d DATE	   --SqlServer2005需要改为datetime
)
GO
--1.2 创建存储过程
IF OBJECT_ID('Proc_TestUtp') IS NOT NULL
	DROP PROC Proc_TestUtp
GO
-- =============================================
-- Author:		yenange
-- Create date: 2021-11-11
-- Description:	用表值参数传值
-- =============================================
CREATE PROCEDURE dbo.Proc_TestUtp
	@p utp_t READONLY 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM @p
END
GO
--1.3 调用
DECLARE @p1 utp_t
INSERT INTO @p1(cid,num,d)
SELECT cid,num,d FROM t

EXEC dbo.Proc_TestUtp @p1
/*
cid	num	d
1	23	2021-11-10
2	45	2021-11-11
*/

--------------- 方法2：使用 XML 参数 --------------- 
--2.1 创建存储过程
IF OBJECT_ID('Proc_TestXML') IS NOT NULL
	DROP PROC Proc_TestXML
GO
-- =============================================
-- Author:		yenange
-- Create date: 2021-11-11
-- Description:	用XML参数传值
-- =============================================
CREATE PROCEDURE dbo.Proc_TestXML
	@x XML 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
	T.C.value('cid[1]','int') AS cid,
	T.C.value('num[1]','int') AS num,
	T.C.value('d[1]','date') AS d
	FROM @x.nodes('//row') AS T(C)
END
GO
--2.2 调用
DECLARE @x XML
SET @x=(SELECT * FROM t FOR XML PATH('row'))

EXEC Proc_TestXML @x
/*
cid	num	d
1	23	2021-11-10
2	45	2021-11-11
*/

GO
--------------- 方法3：使用 JSON 参数 ---------------
--注：必须 SQL Server2016+ 才可以使用
--3.1 创建存储过程
IF OBJECT_ID('Proc_TestJSON') IS NOT NULL
	DROP PROC Proc_TestJSON
GO
-- =============================================
-- Author:		yenange
-- Create date: 2021-11-11
-- Description:	用json字符串传值
-- =============================================
CREATE PROCEDURE dbo.Proc_TestJSON
	@json NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT cid,num,d 
	FROM OPENJSON(@json)
	WITH (
		cid INT 'strict $.cid',
		num INT 'strict $.num',
		d DATE 'strict $.d'
	)
END
GO
--3.2 调用
DECLARE @json NVARCHAR(MAX)
SET @json=(SELECT * FROM t FOR JSON PATH)

EXEC Proc_TestJSON @json
/*
cid	num	d
1	23	2021-11-10
2	45	2021-11-11
*/

对于程序来说， xml, json 来说很简单，因为可以当作字符串来处理； 

但表值参数比较麻烦，可以参考我另一篇博客：

https://blog.csdn.net/yenange/article/details/51488408