CODEWITHSUNDEEP
sqlsql-servert-sql
Storm
Storm

Reputation: 3242

Inserting output parameters from stored procedure directly

I have a stored procedure that returns a pair of output parameters - the ID and the computed value. Is it possible to use a trigger with an insert statement, that inserts those two values directly? Something like this

CREATE TRIGGER Trig_FirstTable ON SecondTable AFTER UPDATE
AS 
BEGIN
INSERT INTO FirstTable (OtherID, OtherValue)
VALUES (@otherID, @otherValue)
FROM StoredProcedure inserted.ID, inserted.Value, @otherID OUTPUT, @otherValue OUTPUT
END

Upvotes: 0

Views: 749

Answers (2)

AdamL
AdamL

Reputation: 13151

You can insert from SP like that:

drop procedure uspTest
GO
create procedure uspTest
AS
select 1 as id, 'x' as val
union all 
select 2,'y'
GO

drop table #temp
GO
create table #temp (
    id int, 
    val char(1) 
)
GO

insert into #temp (id,val)
EXECUTE uspTest
GO

select
    *
from #temp

But you cannot select a subset of columns, so this method will obviously fail if you add more outputs to your SP in the future:

insert into #temp (id)
EXECUTE uspTest

Another way is to store SP results in variables, and then use them for insert.

Upvotes: 0

Joeri
Joeri

Reputation: 225

According to the MSDN documentation you can use INSERT into with EXEC. They give the following example:

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

But I think your stored procedure needs a SELECT statement to return the data instead of only filling the output parameters.

Upvotes: 1

Related Questions