Loop in stored procedure or in client code

Question

From an efficiency and best practices point of view, I appreciate everyones thoughts.

I have a stored procedure that makes (n) copies of a row and inserts the row into the necessary table.

For example. Make a copy of an inventory item and add the copies to inventory.

I see two options for this type of stored procedure.

Option 1:

CREATE PROCEDURE CopyInventory
@InventoryID int
AS

BEGIN

INSERT INTO Inventory (fieldOne, fieldTwo, FieldThree)
(SELECT FieldOne, FieldTwo, FieldThree FROM Inventory WHERE InventoryID = @InventoryID)

END

Using option one I would call the stored procedure multiple times from within a while loop in the client application code.

Option 2:

    CREATE PROCEDURE CopyInventory
    @InventoryID int,
    @CopyCount int
    AS

    BEGIN

    DECLARE @counter int

    SET @counter = 0

    WHILE @counter < @CopyCount

    INSERT INTO Inventory (fieldOne, fieldTwo, FieldThree)
    (SELECT FieldOne, FieldTwo, FieldThree FROM Inventory WHERE InventoryID = @InventoryID)

    END
END

Using option two I would call the stored procedure once from the client application code and pass in a copy count as a parameter.

Option two seems to be the best option to me since it results in only one database call.

I appreciate your thoughts on why you would prefer one over another or neither along with any best practice recommendations.

Answer 1

Of course do it on the server side.

This will save you lots of round-trips between client and server.

Will be even better if you make your loop set-based:

WITH hier(cnt) AS
        (
        SELECT  1 AS value
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @count
        )
INSERT
INTO    Inventory (fieldOne, fieldTwo, FieldThree)
SELECT  FieldOne, FieldTwo, FieldThree
FROM    hier, Inventory
WHERE   InventoryID = @InventoryID