如何将数组传递给存储过程，以便连续插入一些记录

Question

众所周知，SQL Server中没有数组，有一些变通方法可以以某种方式实现这一点，比如表值参数(TVP(和用户定义的数据类型(UDT(，甚至是逗号分隔值(CSV(的参数。

互联网上也有一些例子展示了如何使用它，但大多数都应用了SELECT语句，并直接使用参数，如下所示：

CREATE PROCEDURE dbo.GetOrderList1
(@OrderList VARCHAR(500))
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(600)
SET @SQL =  'SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (' + @OrderList + ')'
EXEC(@SQL)  
END

但我需要使用INSERT语句，并逐一使用参数的每一个部分。

在我的情况下，我通过SignalR向多个参与者发送消息，我也需要将其保存在数据库中。想象一下，我有一个MessageId，我想用这种方式保存它：

ID   |    MessageId   | ReceiverId
-----+----------------+-----------
|                |

我需要我的存储过程只得到一个MessageId，比如100和多个ReceiverId，比如(1001、1002、1003、1004、1005(，并将它们一个接一个地插入相关的表中，类似于以下内容：

EXEC sp_AddToMessageReceivers 100, (1001, 1002, 1003, 1004, 1005)
CREATE PROCEDURE sp_AddToMessageReceivers
@messageId INT, 
@ReceiversIds VARCHAR(500)
AS
BEGIN
DECLARE @i INT = 0
WHILE @i < @ReceiversIds.length
BEGIN
INSERT INTO [dbo].[MessageReceiver] (MessageId, ReceiverIds)
VALUES (@MessageId, @ReceiversIds[i])
SET @i = @i + 1
END
END

我知道如何在存储过程中获取表值参数，但不知道如何在while循环中逐一使用它们。

如果你能为这个问题提供一个解决方案，我真的很合适。

Answer 1

您可以从应用程序端创建一个1-Many关系DataTable，并将其作为Table Value Parameter传递给Stored Procedure，然后可以插入到表中。

例如，首先使用MessageId和ReceiverId创建TVP

CREATE TYPE MessageTableType AS TABLE   
(
MessageId int,
ReceiverId int
)

然后创建这样的存储过程，

CREATE PROCEDURE uspAddToMessageReceivers
@MsgTvp MessageTableType readonly
AS
BEGIN
INSERT INTO [dbo].[MessageReceiver] 
SELECT MessageId, ReceiverId from @MsgTvp
END

然后从应用程序端创建一个DataTable(假设为C#应用程序(，并将其传递给存储过程。

DataTable dataTable = new DataTable();
dataTable.Columns.Add("MessageId", typeof(int));
dataTable.Columns.Add("ReceiverId", typeof(int));
//dataTable.Rows.Add.. Add rows here

Answer 2

您需要创建一个表值函数，该函数从csv值返回一个表形式。

CREATE FUNCTION [dbo].[fn_csvToColumns]
(@CommadelimitedString NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @Result TABLE (Value VARCHAR(100))
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @IntLocation INT
WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
BEGIN
SET @IntLocation =   CHARINDEX(@delimiter, @CommadelimitedString, 0)      
INSERT INTO @Result (Value)
--LTRIM and RTRIM to ensure blank spaces are   removed
SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString, 0, @IntLocation)))   
SET @CommadelimitedString = STUFF(@CommadelimitedString, 1, @IntLocation, '') 
END
INSERT INTO @Result (Value)
SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank     spaces are removed
DELETE FROM @Result 
WHERE Value = ''
RETURN 
END

更改您的存储过程以使用以上功能：

ALTER PROCEDURE sp_AddToMessageReceivers
@messageId INT, 
@ReceiversIds VARCHAR(500)
AS
BEGIN
DECLARE @temp TABLE (tempid INT)
INSERT INTO @temp(tempid)
SELECT value 
FROM dbo.fn_csvToColumns(@ReceiversIds, ',')
WHILE EXISTS (SELECT TOP 1 null FROM @temp)
BEGIN
SELECT TOP 1 @ReceiverIds = tempid 
FROM @temp
INSERT INTO [dbo].[MessageReceiver] (MessageId, ReceiverIds)
VALUES (@MessageId, @ReceiverIds)
DELETE FROM @temp 
WHERE tempid = @ReceiverIds
END
END

Answer 3

我会使用一个split函数(这里可以找到很好的例子(。

示例：

INSERT INTO yourTable (messageId, ReceiversId)
SELECT @messageId, fn_ssm.Item
FROM dbo.SplitStrings_Moden fn_ssm (@ReceiversIds, N',')

我强烈反对任何使用WHILE循环之类的方法，尤其是像上面的例子那样嵌套循环，因为这可能会严重影响代码性能！