如何将一组静态列列数据同时传递到存储过程中？

My Stored procedure is

我的存储过程是

   ALTER PROCEDURE [dbo].[Register]
    @Id        int,
    @Name nvarchar(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Group (Id, Name) 
        VALUES(@Id, @Name)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END
GO

I want pass like this data for insert into this table

我希望传递像这样的数据插入到这个表中

@Id = 1 
@Name = 'test1,test2,test3,test4,test5'

and result like this

结果是这样的

Id   Name
1    test1
1    test2
1    test3
1    test4
1    test5

Kindly share the best possible way to achieve this.

请分享实现这一目标的最佳方式。

3 个解决方案

#1

You could make use of TVPs:

你可以使用TVP：

Create a User Defined Table Type

创建用户定义的表类型

CREATE TYPE [dbo].[TestTableType] AS TABLE(
                                            Id [int] NOT NULL,
                                            Name varchar(10)
                                          )

Pass it as a parameter to your stored procedure

将其作为参数传递给存储过程

ALTER PROCEDURE [dbo].[Register]
    @testTable TestTableType READONLY
AS
BEGIN
BEGIN TRY
    INSERT INTO dbo.Group (Id, Name) 
    SELECT *
    FROM @testTable

    SELECT 0
END TRY
BEGIN CATCH
    SELECT -1
END CATCH
END

You could find more information in the documentation and please note that the TVP, when used as a parameter to a stored procedure has to be declared as READONLY.

您可以在文档中找到更多信息，请注意，当用作存储过程的参数时，TVP必须声明为READONLY。

#2

SQL Server 2016 offers string_split(), which does exactly what you want to do:

SQL Server 2016提供了string_split（），它完全符合您的要求：

ALTER PROCEDURE [dbo].[Register] (
    @Id   int,
    @Name nvarchar(max)
) AS
BEGIN
    BEGIN TRY
        WITH s AS (
            SELECT STRING
        INSERT INTO dbo.Group (Id, Name) 
            VALUES(@Id, @Name)
            SELECT i.id, s.val
            FROM (SELECT @id as id) s CROSS APPLY
                 STRING_SPLIT(name, ',') s(val)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END;
GO

For earlier versions of SQL Server you can find code for string_split() on the web.

对于早期版本的SQL Server，您可以在Web上找到string_split（）的代码。

#3

First create yourself a split function, like this:

首先创建一个split函数，如下所示：

create FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(max) -- List of delimited items
  , @sDelimiter VARCHAR(5) -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(max))

BEGIN
DECLARE @sItem VARCHAR(max)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

You will then be able to do something along these lines:

然后，您将能够按照以下方式执行某些操作：

declare @test table
(
id int,
name varchar(20)
)

declare @list varchar(MAX) = 'test1,test2,test3,test4,test5'
declare @id int = 1

INSERT INTO @test SELECT @id, * from dbo.fnSplit(@list,',')

SELECT * FROM @test

This returns

这回来了

1   test1
1   test2
1   test3
1   test4
1   test5

Obviously you will not need the select, but I just wanted to show that the insert worked!

显然你不需要选择，但我只是想表明插入工作！

#1