How can I pass a list of column data into a stored procedure?
如何将列数据列表传递到存储过程?
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,2,3,4,5
@Name = 'test1,test2,test3,test4,test5'
and result like this
结果是这样的
Id Name
1 test1
2 test2
3 test3
4 test4
5 test5
1 个解决方案
#1
9
A "list" or "array" in SQL Server is ..... a table. So if you're on SQL Server 2008 or newer (you didn't specify), then use the table-valued parameter feature of SQL Server to pass a table of value to your stored procedure
SQL Server中的“列表”或“数组”是一个表。因此,如果您使用的是SQL Server 2008或更高版本(未指定),则使用SQL Server的表值参数功能将值表传递给存储过程
-- Create a table type to match your input parameters
CREATE TYPE IdNameTable AS TABLE
( ID INT, Name NVARCHAR(50) );
GO
-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
@Values IdNameTable READONLY
AS
BEGIN
BEGIN TRY
INSERT INTO dbo.Group (Id, Name)
-- get the values from the table type parameter
SELECT
Id, Name
FROM
@Values
SELECT 0
END TRY
BEGIN CATCH
SELECT -1
END CATCH
END
GO
See the extensive and freely available SQL Server Books Online documentation for more details on the table-valued parameter feature and how to use it
有关表值参数功能及其使用方法的更多详细信息,请参阅广泛且免费提供的SQL Server联机丛书文档
If you want to use this from T-SQL, use this code:
如果要从T-SQL中使用它,请使用以下代码:
-- declare a variable of that table type
DECLARE @InputTable IdNameTable
-- insert values into that table variable
INSERT INTO @InputTable(ID, Name)
VALUES (1, 'Test 1'), (2, 'Test 2')
-- execute your stored procedure with this table as input parameter
EXECUTE [dbo].[Register] @InputTable
If you want to use this from C# or VB.NET, see Michael Edenfield's link in comments.
如果您想在C#或VB.NET中使用它,请参阅Michael Edenfield的评论链接。
#1
9
A "list" or "array" in SQL Server is ..... a table. So if you're on SQL Server 2008 or newer (you didn't specify), then use the table-valued parameter feature of SQL Server to pass a table of value to your stored procedure
SQL Server中的“列表”或“数组”是一个表。因此,如果您使用的是SQL Server 2008或更高版本(未指定),则使用SQL Server的表值参数功能将值表传递给存储过程
-- Create a table type to match your input parameters
CREATE TYPE IdNameTable AS TABLE
( ID INT, Name NVARCHAR(50) );
GO
-- change your stored procedure to accept such a table type parameter
ALTER PROCEDURE [dbo].[Register]
@Values IdNameTable READONLY
AS
BEGIN
BEGIN TRY
INSERT INTO dbo.Group (Id, Name)
-- get the values from the table type parameter
SELECT
Id, Name
FROM
@Values
SELECT 0
END TRY
BEGIN CATCH
SELECT -1
END CATCH
END
GO
See the extensive and freely available SQL Server Books Online documentation for more details on the table-valued parameter feature and how to use it
有关表值参数功能及其使用方法的更多详细信息,请参阅广泛且免费提供的SQL Server联机丛书文档
If you want to use this from T-SQL, use this code:
如果要从T-SQL中使用它,请使用以下代码:
-- declare a variable of that table type
DECLARE @InputTable IdNameTable
-- insert values into that table variable
INSERT INTO @InputTable(ID, Name)
VALUES (1, 'Test 1'), (2, 'Test 2')
-- execute your stored procedure with this table as input parameter
EXECUTE [dbo].[Register] @InputTable
If you want to use this from C# or VB.NET, see Michael Edenfield's link in comments.
如果您想在C#或VB.NET中使用它,请参阅Michael Edenfield的评论链接。