Insert list of values into a SQL Server table with stored procedure

Question

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

marc_s · Accepted Answer · 2014-07-27 09:43:04Z

12

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.

edited Jul 27, 2014 at 9:43

answered Jul 27, 2014 at 8:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Edenfield Over a year ago

msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx for how to use these from C#

Jesuraja Over a year ago

It will be helpful, if you update how to pass the data to the procedure parameter...

SH.Developer Over a year ago

@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 Over a year ago

@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$

user5708151 Over a year ago

@marc_s thanks, but I can't drag sp to dbml in visual studio2015 !?

Collectives™ on Stack Overflow

Insert list of values into a SQL Server table with stored procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related