INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using OUTPUTand Table variables

Using OUTPUTand Table variables

(OP)
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.

CODE

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

RE: Using OUTPUTand Table variables

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....

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.

RE: Using OUTPUTand Table variables

(OP)
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

RE: Using OUTPUTand Table variables

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/ms1911...

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close