CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresinsert
Adnand
Adnand

Reputation: 562

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

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?

Upvotes: 0

Views: 1021

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

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

Upvotes: 1

Related Questions