Insert list of values into a SQL Server table with stored procedure
12,437
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
-- 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
If you want to use this from T-SQL, use this code:
-- 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.
Author by
SH.Developer
Updated on June 13, 2022Comments
-
SH.Developer almost 2 years
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
-
Michael Edenfield almost 10 yearsmsdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx for how to use these from C#
-
Jesuraja almost 10 yearsIt will be helpful, if you update how to pass the data to the procedure parameter...
-
SH.Developer almost 10 years@marc_s thank's veryyyy veryyy,but one questio. how i use exec stored procedure ? pass value? after i run this stored procedure , and want AlTER it i get this error he type 'IdNameTable' already exists, or you do not have permission to create it. please help me
-
marc_s almost 10 years@user3807529: I added code to show how to use it from T-SQL - if you want to use it from C# or VB.NET, see Michael's link he's posted in the comments$
-
Admin almost 8 years@marc_s thanks, but I can't drag sp to dbml in visual studio2015 !?