How to insert multiple records into a table in one stored procedure call

Question

I have a stored procedure that accepts a string such as A, B, C...etc. I want to split the string and insert each letter as one record into a table. The result should be:

col1 col2
1    A
2    B
3    C

I could use cursor, but cursor is kind of slow if I call this stored procedure from my web page. Is there any better solution?

score 2 · Accepted Answer · edited May 23 '17 at 12:33

Instead of passing a comma-separated string, pass a table-valued parameter. First, create a table type in your database:

CREATE TYPE dbo.Strings AS TABLE(String NVARCHAR(32));

Then your stored procedure:

CREATE PROCEDURE dbo.InsertStrings
  @Strings dbo.Strings READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.Table(Col2) -- assuming col1 is an IDENTITY column?
    SELECT String FROM @Strings;
END
GO

Then in your C# code or whatever, you just pass a DataTable as a Structured parameter. Example here and background here.

If you really don't want to do this, because it's too hard or whatever, then you can use a much less efficient string splitting function, e.g.

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

Then your procedure is:

CREATE PROCEDURE dbo.InsertStrings
  @Strings NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.Table(Col2) -- assuming col1 is an IDENTITY column?
    SELECT [Value] FROM dbo.SplitString(@Strings, ',');
END
GO

instead of using table-valued parameter, can I split the string and insert each 'token' into a temp table? — GLP, Dec 09 '13 at 18:11

How to insert multiple records into a table in one stored procedure call

1 Answers1