在使用过程中,传一个表(数据集)的需求是真实存在的,经常有人问。
于是,总结了以前零零散散的知识,汇集为本文,分享给读者。
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 来说很简单,因为可以当作字符串来处理;
但表值参数比较麻烦,可以参考我另一篇博客: