How to Use Table-Valued Parameter as Output parameter for stored procedure ?
Is it possible 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 procedure which 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
This is an older post, but it was near the top when I was searching for "Table-Valued Parameter as -sql server stored procedure 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