Multiple inserts in a single stored procedure?

Question

TableName: Information

Stored procedure that inserts data into the above table.

CREATE PROCEDURE sp_insert_information
(
    @profileID as int,
    @profileName as varchar(8)
    @profileDescription as varchar(100)
)
AS
BEGIN
   INSERT INTO information(profileid, profilename, profiledescription)
   VALUES (@profileID, @profileName, @profileDescription);
END

I call this procedure from .NET, is there a way to do multiple inserts if I pass profileID's as a comma separated parameter? (can I use split function?)

I can either loop through the profileID's and send 1 by 1 to procedure, however my data is going to be the same except different profileID.

Table data (with 3 columns):

1 profileUnavailable  User Error
2 profileUnavailable  User Error
3 profileUnavailable  User Error
4 profileUnavailable  User Error
5 profileUnavailable  User Error

Any other approaches that I can try to do this in a single shot?

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Apr 14 '14 at 18:44

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

You have a couple options:

SqlBulkInsert - You can create a dataset that you can dump to the table. This is useful for many inserts. This will bypass the procedure altogether.

Table Valued Parameters - You can use a table value parameter as a parameter of the stored procedure, again manipulating data using a dataset.

The CSV Parameter with string split IS an option, but I would recommend one of the above over it.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 14 '14 at 17:29

Khan

17,904
5
47
59

But if if it's through C# code (like OP have mentioned) then it will be row by row insert only. – Rahul Apr 14 '14 at 17:54
@Rahul Care to elaborate? Neither of my solutions are row by row. – Khan Apr 14 '14 at 17:57

Daniel A. White · Answer 2 · 2014-04-14T17:26:42.190

0

Nope. That sproc does one insert at a time as it is written presently. You have to invoke it separately.

You might also consider wrapping that up into a transaction so if one fails, all of them won't be committed.

edited Apr 14 '14 at 17:26

answered Apr 14 '14 at 17:20

Daniel A. White

187,200
47
362
445

score 0 · Answer 3 · answered Apr 14 '14 at 17:44

My favourite technique up to some years ago was to have an arsenal of splitting functions, that could split a delimited list of homogeneous values (e.g. all integers, all booleans, all datetimes, etc.) into a table variable. Here is an example of such a function.

CREATE FUNCTION [dbo].[fn_SplitInt](@text varchar(8000), 
                                    @delimiter varchar(20) = '|')
RETURNS @Values TABLE
(    
  pos int IDENTITY PRIMARY KEY,
  val INT
)

AS
BEGIN

  DECLARE @index int 
  SET @index = -1 

  -- while the list is not over...
  WHILE (LEN(@text) > 0) 
    BEGIN  
      -- search the next delimiter
      SET @index = CHARINDEX(@delimiter , @text)  

      IF (@index = 0) -- if no more delimiters (hence this field is the last one)
        BEGIN
          IF (LEN(@text) > 0) -- and if this last field is not empty
            INSERT INTO @Values VALUES (CAST (@text AS INT))  -- then insert it
          ELSE                -- otherwise, if this last field is empty
            INSERT INTO @Values VALUES (NULL)                 -- then insert NULL
          BREAK  -- in both cases exit, since it was the last field
        END  
      ELSE             -- otherwise, if there is another delimiter
        BEGIN   
          IF @index>1   -- and this field is not empty
            INSERT INTO @Values VALUES (CAST(LEFT(@text, @index - 1) AS INT)) -- then insert it
          ELSE          -- otherwise, if this last field is empty
            INSERT INTO @Values VALUES (NULL)                                 -- then insert NULL
          SET @text = RIGHT(@text, (LEN(@text) - @index))  -- in both cases move forward the read pointer,
                                                           -- since the list was not over
        END  
    END
  RETURN
END

When you have a set of functions like these, then your problem has a solution as simple as this one:

CREATE PROCEDURE sp_insert_information
(
 @profileID as varchar(2000),
 @profileName as varchar(8)
 @profileDescription as varchar(100)
)
AS
BEGIN

  DECLARE @T TABLE (Id int)
  INSERT INTO @T (Id) 
    SELECT val FROM dbo.fn_SplitInt(@profileID)
  INSERT INTO information(profileid, profilename,profiledescription)
    SELECT Id, @profileName, @profileDescription
      FROM @T

END

But today it might be quicker to execute, and even require less coding, to generate an XML representation of the data to insert, then pass the XML to the stored procedure and have it INSERT INTO table SELECT FROM xml, if you know what I mean.

score 0 · Answer 4 · answered Oct 20 '14 at 05:28

   WHILE len(@ProfileId) > 0
    BEGIN
      DECLARE @comm int= charindex(',',@ProfileId)

      IF @comm = 0 set @comm = len(@ProfileId)+1

          DECLARE @Profile varchar(1000) = substring(@ProfileId, 1, @comm-1)

          INSERT INTO Information(ProfileId,ProfileName,ProfileDescription) 
          VALUES (@ProfileId,@ProfileName,@ProfileDescription)

     SET @ProfileId= substring(@ProfileId, @comm+1, len(@ProfileId)) 

    END

Multiple inserts in a single stored procedure?

4 Answers4