Execute a stored procedure to update each row before inserting

Question

I'm writing an INSTEAD OF INSERT trigger to intercept the inserted data and fill in column2.

I need to execute a stored procedure which returns the value to insert into column2.
The stored procedure returns a different value each time it is executed (crypto prng).

Therefore, if someone inserts 10 rows, my trigger should execute the stored procedure 10 times, and populate each row with one of the values returned.

I've heard that cursors should rarely be used for something like this.
But I'm not sure if a standard loop is the best approach either.

Is there a more streamlined method of accomplishing this within a SELECT, UPDATE, or INSERT statement?

CREATE TRIGGER dbo.table1_instead_of_insert
ON dbo.table1
INSTEAD OF INSERT
AS BEGIN
    DECLARE @i BIGINT ;
    EXECUTE dbo.proc1 @1 ;
    --proc1 sets the value of @i to a different value each time proc1 is executed
    --(crypto prng)

    --Update the value of column2 in INSERTED, setting it to the value of @i

    --Repeat this process for each row in INSERTED,
      --with proc1 returning a different value for each row

    INSERT INTO dbo.table1 ( column1 , column2 )
        SELECT column1 , column2 FROM INSERTED ;
END
GO

Answer 1

Try this:

CREATE TRIGGER dbo.table1_instead_of_insert
ON dbo.table1
INSTEAD OF INSERT
AS BEGIN

    CREATE TABLE #DataSource
    (
        [RowID] INT IDENTITY(1,1) 
       ,[Column1] VARCHAR(12) -- use your type
       ,[Column2] VARCHAR(12) -- use your type
    );

    INSERT INTO #DataSource ([Column1], [Column2])
    SELECT [Column1], [Column2]
    FROM inserted;

    DECLARE @RowID TINYINT = 1;
    DECLARE @i BIGINT ;

    WHILE(EXISTS(SELECT 1 FROM #DataSource WHERE [RowID] = @RowID))
    BEGIN;

         EXECUTE dbo.proc1 @i ;

         UPDATE #DataSource
         SET [Column2] = @i
         WHERE [RowID] = @RowID;

        SET @RowID = @RowID + 1;
    END;    

    INSERT INTO dbo.table1 ( column1 , column2 )
        SELECT column1 , column2 FROM #DataSource ;
END
GO

This is bad. Very bad. It can easily kill your performance. In a environment where a lot of inserts are performed, you will execute this stored procedure for each row.

It's a common practice for developers with less database experience to process rows one by one. You should try to modify this stored procedure to be able to generate rows of @i value - in such way you will be able to skip the loop.

You should check this link, too.