Best way to insert values multiple times from data layer to stored procedure?

Question

Hi I have DAL Layer, from where invoking a stored procedure to insert values into the table.

E.g.:-

CREATE PROCEDURE [dbo].[DataInsert]
    @DataName nvarchar(64)
AS
BEGIN
    INSERT INTO 
         table01 (dataname) 
        VALUES 
    (@dataname)
END

Now as requirement changed, per client request i have to add values 5 times. So what is the best practice?

Do i call this Stored Procedure 5 times from my DAL?

or

Pass all the values (may be comma separated) to storedprocedure in one go and then let the stored procedure add it for 5 times?

BTW. Its not always 5 times. It is changeable.

Answer 1

btw this proc works - I've just written and tested it see results below!

CREATE PROCEDURE [dbo].[DataInsert]
     @DataName nvarchar(max) AS  
BEGIN  
DECLARE @pos SMALLINT, @str VARCHAR(max)  

WHILE @DataName <> ''  
BEGIN  
    SET @pos = CHARINDEX(',', @DataName)  
    IF @pos>0  
        BEGIN  
            SET @str = LEFT(@DataName, @pos-1) 
            SET @DataName = RIGHT(@DataName, LEN(@DataName)-@pos)  
        END  
    ELSE  
        BEGIN  
            SET @str = @DataName 
            SET @DataName = ''  
        END  

   INSERT INTO table01 VALUES(CONVERT(VARCHAR(100),@str)) 

END  
END  
GO

then run it: -

EXEC @return_value = [dbo].[DataInsert] @DataName = N'five, bits, of, your, data'

*rows from table01: *

five

bits

of

your

data

(5 row(s) affected)

Answer 2

You could create a user-defined table type;

CREATE TYPE [dbo].[SomeInfo] AS TABLE(
[Id] [int] NOT NULL, 
[SomeValue] [int] NOT NULL )

Define your stored proc as such;

CREATE PROCEDURE [dbo].[AddSomeStuff]
    @theStuff [SomeInfo] READONLY
AS
BEGIN
    INSERT INTO SOMETABLE ([...columns...])
    SELECT [...columns...] from @theStuff
END

Then you'll need to create a datatable (called table below) that matches the schema and call the stored proc as so;

var cmd = new SqlCommand("AddSomeStuff", sqlConn) {CommandType = CommandType.StoredProcedure};

var param = new SqlParameter("@theStuff", SqlDbType.Structured) {Value = table};
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

Answer 3

I'd either call your proc repeatedly(that would be my choice), or else you could use XML to pass in a list of values as a single parameter.

http://support.microsoft.com/kb/555266

Answer 4

Instead of fancy SQL code that is difficult to maintain and is not scalable, I would simply go to invoking your stored procedure multiple times.

If performance or transactional behavior is an issue, you can consider to send the commands in a single batch.

You talked about 5 insert. If the number of record to insert is much greater, you could consider bulk insert as well.