Table-Valued Parameter as Output parameter for stored procedure
No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp
table.
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
in addition to the nicely put answer by remus including the link he provided
How to Share Data between Stored Procedures
there are situations where you get the following error messages when saving the results of a stored procedure to a table:
An INSERT EXEC statement cannot be nested.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
and when this happens on my own stored procedures that I develop for my own use
for example a tool to tell me from a login
all the AD groups it belongs to and all their permissions in all databases in a server
I create a temp table outside the procedure and pass its name as a parameter
--===============
-- this way below it works, by passing a temp table as a parameter
--===============
if OBJECT_ID('tempdb.dbo.#my_table') IS NOT NULL
DROP TABLE #my_table
CREATE TABLE #my_table(
db nvarchar(128) COLLATE Latin1_General_CI_AS NULL,
permission_type nvarchar(128) COLLATE Latin1_General_CI_AS NULL,
login_ nvarchar(128) COLLATE Latin1_General_CI_AS NULL,
role_ nvarchar(128) COLLATE Latin1_General_CI_AS NULL,
Obj nvarchar(517) COLLATE Latin1_General_CI_AS NULL,
Permission nvarchar(128) COLLATE Latin1_General_CI_AS NULL,
script nvarchar(1008) COLLATE Latin1_General_CI_AS NULL
)
exec sp_GetLoginDBPermissionsX
@Login='my_loginname',
@debug=0,
@where_to_save ='#my_table'
select *
from #my_table
and inside the procedure, after all the calculations, when I am returning the final data (below an example) I check whether we are outputting to a table or just back to the screen and create the script dynamically.
select @sql = case when @where_to_save IS not null then
'
insert into ' + @where_to_save + '(db,Permission_Type,login_,role_,obj,Permission,script) '
else '' end +
'
SELECT
J.db,
J.Permission_Type,
J.login_,
J.role_,
J.Obj,
J.Permission,
J.script
FROM #tablewithpermissions J
WHERE J.login_ IN ( SELECT L1.LOGIN_FROM COLLATE Latin1_General_CI_AS FROM #logins L1)
OR J.role_ IN ( SELECT L1.LOGIN_FROM COLLATE Latin1_General_CI_AS FROM #logins L1)
ORDER BY J.DB, J.[permission_order]
'
--print(@sql)
EXEC(@SQL)
After that you have either the info you need on the screen, or if you have passed a temp table as a parameter, it will have the data now.
this is one solution I found, but I only use it for my own works DBA
otherwise this will be considered high risk for Sql Injection.