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

  • 2
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值