How to use stored procedure return value into an insert statement?

Question

I have a stored procedure like

CREATE PROCEDURE GetSerial (@param1 int, @param2 int)
AS
BEGIN
    -- do some insert/updates, so I can't use function
    DECLARE @value AS int;
    SET @value = 3;
    return @value;
END

Now I declare a table variable

DECLARE @Serials AS TABLE 
(
   ID int,
   Value int
)

Now I wanna fill this table like

INSERT INTO @Serials (ID, Value)
SELECT 1, GetSerial(1,2) -- *How can I call this?

So, can anyone help me how can i call the GetSerial stored procedure inside the SELECT statement to fill my table?

You cannot call a stored procedure from a SELECT statement... You have to use `Execute YourStoredProcedureName` — Neo, Dec 03 '18 at 15:24
This should help you: https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — Neo, Dec 03 '18 at 15:27

score 1 · Answer 1 · answered Dec 03 '18 at 15:36

I recommend you avoid getting into this pattern/thinking, because stored procedures only return INTs, and those ints are really intended to describe how well the operation went, not a result/data from the operation. Example: 0 => failed, 1=> succeeded. Not GetAgeInYears() => 29

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017 has a lot of info and concrete examples but in your specific case you'd need to execute the procedure and capture the result code into a variable then insert that:

DECLARE @ret INT;
EXEC @ret = GetSerial(1,2);
INSERT INTO @Serials VALUES(1, @ret);

Really you'd be better off using an output parameter or resultset if you have many values to return. See the above link for more

How to use stored procedure return value into an insert statement?

1 Answers1