Can Table-Valued Parameter used as Output parameter for stored procedure?
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int not null ,name varchar(50) not null ,value varchar(50) not null PRIMARY KEY (id) ) GO --Now I want to create stored procedu whic is going to send output type I created, --But it looks like it is inpossible, at least in SQL2008 create PROCEDURE [dbo].sp_test @od datetime ,@do datetime ,@poruka varchar(Max) output ,@iznos money output ,@racun_stavke dbo.typ_test READONLY --Can I Change READONLY with OUTPUT ? AS BEGIN SET NOCOUNT ON; /*FILL MY OUTPUT PARAMS AS I LIKE */ end
What are output parameters in stored procedure?
Yes, Passing table-valued parameters to a stored procedure is a three-step process: Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter.
This is an older post, but it was near the top when I was searching for "Table-Valued Parameter as Output parameter for stored procedure". While it is my understanding that you cannot pass a table-valued parameter as an output parameter, I would imagine the goal is to use that table-valued output parameter as a table-valued input parameter in another procedure. I will show an example for how I made this work.
First, create some data to work with:
create table tbl1 ( id int, fname varchar(10), gender varchar(10) ); create table tbl2 ( id int, lname varchar(10) ); insert into tbl1 values (1,'bob' ,'m'), (2,'tom' ,'m'), (3,'sally','f') ; insert into tbl2 values (1,'jones' ), (2,'johnson' ), (3,'smith' ) ;
Next, create a stored procedure to capture some of the data. Normally, this would be where you are trying to create a table-valued output parameter.
create procedure usp_OUTPUT1 @gender varchar(10) as Begin select id from tbl1 where gender = @gender End
Additionally, you will want to create a data type (table type) where the data from the first stored procedure can be passed as the input parameter for the next stored procedure.
create type tblType as Table (id int)
Next, create the second stored procedure that will accept the table-valued parameter. create procedure usp_OUTPUT2 @tblType tblType readonly --referencing the type created and specify readonly as begin select lname from tbl2 where id in (select id from @tblType) end
Granted, this is not a true table-valued output parameter, but it will likely produce results similar to what you would be looking for. Declare your table-valued parameter, fill it with data by executing the stored procedure into it, then use it as the input variable for the next procedure.
Declare @tblType tblType insert into @tblType execute usp_OUTPUT1 'm' execute usp_OUTPUT2 @tblType
SQL server stored procedure output parameter Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant.