sql - 在 SQL Server 2008 中插入表使用数组

Question

这是我的 table :

电话:Id、MemberId、Number、PhoneType、IsDefault

我需要一个存储过程来获取一个numbers 数组和一个memberId 作为参数，并将它们全部插入到Phone 表中。我不能一个一个地传递数字，我需要插入所有数字或不插入任何数字。我想将数字作为以逗号分隔的列表 Nvarchar 传递，例如 '0412589, 0425896, 04789652' 我必须提到 MemberId 始终不变并且通过参数传递，PhoneType 总是常量 ='phone'，对于数组的第一个数字 IsDefault = 1 对于其他 IsDefault = 0 如何从 numbers 中选择 Numbers 并在插入命令中设置其他列？或者您有另一个传递数字数组的建议？

Answer 1

您可以使用表值参数。所以如果你有这样的类型:

CREATE TYPE PhoneNumberType AS TABLE 
( number  VARCHAR(50));
GO

那么你的程序会是这样的:

CREATE PROCEDURE dbo. usp_InsertPhoneNumbers
    @TVP PhoneNumberType READONLY
AS 
INSERT INTO Phone(number,....)
SELECT
    number,
    ....
FROM
    @TVP

引用 here

编辑

它就像一张 table 一样工作，所以很容易做到:

DECLARE @TVP PhoneNumberType

INSERT INTO @TVP
VALUES
    ('0412589'),('0425896'),('04789652')

EXEC usp_InsertPhoneNumbers @TVP

或者，如果您将它作为一个字符串使用，那么您需要将其拆分。这是一个简洁的拆分函数:

CREATE FUNCTION dbo.Split (@s varchar(512),@sep char(1))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO 

然后你这样调用程序:

DECLARE @TVP PhoneNumberType
DECLARE @textToSplit VARCHAR(512)='0412589, 0425896, 04789652'
INSERT INTO @TVP
SELECT
    split.s
FROM
    dbo.Split(@textToSplit,',') AS split

EXEC usp_InsertPhoneNumbers @TVP