How can I use the OUTPUT from a stored procedure within an INSERT statement?

Question

I'm trying to copy data from one table to another and add one column to the target table that isn't present in the source table using INSERT INTO and a stored procedure to generate the value for the additional column.

The target table has counter field (integer) that needs to be incremented for every row inserted (it is not an IDENTITY column, incrementing this counter is handled by other code).

The Counter table has counters for multiple tables and need to pass the Counter Name as a parameter to the stored procedure and use output as the additional column for the target table.

I have a stored procedure and can pass it a parameter (counter name, in example below counter name is "Counter_123") and it has as output value as the new counter value.

I can run the stored procedure like this and it works fine:

declare @value int
exec GetCounter 'Counter_123', @value output
PRINT @value

In this case if the counter was 3, the output is 4.

Here is what I'm trying to do in my INSERT INTO:

INSERT INTO Target_Table (col1, col2, col3, uspGetCounter 'Counter_123')
SELECT (val1, val2, val3) 
FROM Source_Table
WHERE ...

Error returned is syntax error near 'Counter_123'

I've also tried to put the stored procedure in the values list but that doesn't work either.

What is the syntax to call a stored procedure, pass it a parameter, and use the output in the INSERT INTO query as a value for a column?

Here is the stored procedure code for reference:

CREATE PROCEDURE [dbo].[uspGetCounter] 
    
    @CounterName varchar(30) = '', 
    @LastValue int OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

    if not exists (select * from Counter where COUNTER_NAME=@CounterName) 
       insert Counter (COUNTER_NAME, LAST_VALUE)
       values (@CounterName,0)

    select @LastValue=LAST_VALUE+1
       from Counter (holdlock)
       where COUNTER_NAME= @CounterName

    update Counter
       set LAST_VALUE=@LastValue,
           LAST_UPDATED=getdate(),
           from Counter
       where COUNTER_NAME=@CounterName

    COMMIT TRANSACTION
    
END

Answer 1

Assuming you really need to roll your own sequence generator, because using a built in sequence is going to be much easier, then with your current logic you can only insert a single row at a time, because your stored procedure provides no mechanism to allocate a block of values in one go. You would therefore need a loop of some sort, maybe a cursor e.g.

DECLARE @Counter int, @Val1 int, @Val2 int, @Val3 int;

DECLARE MyCursor CURSOR LOCAL FOR
    SELECT val1, val2, val3 
    FROM Source_Table
    WHERE ...;

WHILE 1 =1 BEGIN
    FETCH NEXT FROM MyCursor INTO @Val1, @Val2, @Val3;
    IF @@FETCH_STATUS != 0 BREAK;

    EXEC uspGetCounter 'Counter_123', @Counter OUTPUT;

    INSERT INTO Target_Table (Val1, Val2, Val3, CounterCol)
        VALUES (@Val1, @Val2, @Val3, @Counter);
END;

Where you run your SP for each row you need to insert and obtain a new sequence value.

If using this approach I would also modify your SP as follows for performance:

-- Attempt an update and assignment in a single statement
UPDATE dbo.[Counter] SET
  @LastValue = LAST_VALUE = LAST_VALUE + 1
  , LAST_UPDATED = GETDATE()
WHERE COUNTER_NAME = @CounterName;

-- If doesn't exist (less likely case as only the first time the counter is accessed)
IF @@ROWCOUNT = 0 BEGIN
    SET @LastValue = 1;
    -- Create a new record if none exists
    INSERT INTO dbo.[Counter] (COUNTER_NAME, LAST_VALUE, LAST_UPDATED)
        SELECT @CounterName, @LastValue, GETDATE();
END;

Which reduces access and speeds up the SP.

---

However, again assuming you need to roll your own sequence, if you are using this to copy data frequently you would be much better off modifying your stored procedure to allocate a block of values in one go i.e. when you call your SP you pass a parameter @NumValuesRequired and then your return value is the first of a block that long. Then you can carry out your copy in a single go e.g.

DECLARE @RecordCount int, @CounterStart int;

SELECT @RecordCount = COUNT(*)
FROM Source_Table
WHERE ...;

EXEC uspGetCounter2 'Counter_123', @RecordCount, @CounterStart OUTPUT;

INSERT INTO Target_Table (Val1, Val2, Val3, CounterCol)
    SELECT val1, val2, val3, @CounterStart + ROW_NUMBER() OVER () - 1
    FROM Source_Table
    WHERE ...;

Answer 2

First of all, the the procedure is in the wrong place. That part of the INSERT statement expects column names, not column values. Anything you do with the procedure would go in the SELECT clause for the statement, if you could run it that way (which you can't).

That out of the way, the existing Counter table and stored procedure are obsolete. They exist to solve a problem that has since been rolled into SQL Server as a core feature. Therefore the BEST solution here is converting the counter (all of them) to use a SEQUENCE and NEXT VALUE FOR syntax.

Then, assuming you have primed the sequence to the correct value the INSERT would look like this:

INSERT INTO Target_Table (col1, col2, col3, [Counter_123])
SELECT (val1, val2, val3, NEXT VALUE FOR Counter_123) 
FROM Source_Table
WHERE ...

If for some reason that's not an option, I would roll the counter logic into the query to run in a single transaction, rather many many transactions, like this:

BEGIN TRANSACTION

DECLARE @LastValue int;
SELECT @LastValue = LAST_VALUE
       FROM Counter (holdlock)
       WHERE COUNTER_NAME = 'Counter_123'

INSERT INTO Target_Table (col1, col2, col3, [Counter_123])
SELECT val1, val2, val3, @LastValue + ROW_NUMBER() OVER (ORDER BY val1, val2, val3) 
FROM Source_Table
WHERE ...

UPDATE Counter
SET LAST_VALUE = (SELECT MAX([Counter_123]) FROM [Target_Table])
     ,LAST_UPDATED=current_timestamp
WHERE COUNTER_NAME = 'Counter_123'

COMMIT