Using OUTPUTand Table variables

[wbodger]

If I have stored procedure A which calls stored procedure B to do an insert and return the ID of the inserted record, can I create a Table variable in stored procedure B, fill that Table variable using OUTPUT and then retrieve the value in that Table variable in the calling stored procedure (A) to use as a parameter to pass to stored procedure C? Or would I need to declare the table variable in the calling stored procedure and send it as an out parameter when calling the second stored procedure i.e.

create procedure A
AS
BEGIN
DECLARE @InsertedId TABLE (ID int)
EXEC B @SomeID, @InsertedId OUTPUT
DECLARE @NewId in = SELECT ID FROM @InsertedId
Exec C @NewId
END

create procedure B @SomeId int, @InsertedId int OUTPUT
AS
BEGIN
insert into table
OUTPUT Inserted.Id @InsertedId
select * from someothertable
END

Does that make sense or am I making it too difficult?

Thanks!
wb

 

[Olaf Doschke]

Two more usual ways to know the data, especially new ID of a record is

1. @@IDENTITY, IDENT_CURRENT('table'), and SCOPE_IDENTITY()
2. Insert-Triggers (Before Insert/Insteadof Insert/After Insert)

I assume though, you don't want a general trigger mechanism. You already use the inserted pseudo table in the OUTPUT clause, you should rather do it as shown in Example A of the OUTPUT clause help topic:

https://msdn.microsoft.com/en-us/library/ms177564.aspx

Then a procedure is the wrong thing to use, because first of all procedure B would need to define @InsertedID as table parameter, not as int, and you can't do that, you can only pass in a READONLY table and not define a table as output parameter. There is another thing for returning tables as output, table valued user defined functions - so, well, the reason for the stored proc restriction is there already is another mechanism for returning tables. You'd need to define a function creating the @Inserted table and use that instead of @Inserted in procedure A. You'd also not declare @Inserted in A, that declaration would be done in the function inserting data and returning the @Inserted Table as it's main return 'value'.

Bye, Olaf.

 

[wbodger]

Thanks, Olaf. I was afraid that was the answer, t was hoping I had missed something. I will stick with scope_identity() for these simple use cases.

Thanks,
Willie

 

[Olaf Doschke]

You can't create a stored proc doing what you want to do, bit it's not that complicated to create a table valued function instead, is it? It's the same kind of "beast" just always returning a table instead of scalar values or output parameters. And after you defined it, you can JOIN or SELECT FROM a table valued function as if it was a table.

https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

Bye, Olaf.