All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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
there are situations where you get the following error messages when saving the results of a stored procedure to a table:
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.
A table could be converted to JSON (SQL Server 2016 or later) and passed as an nvarchar parameter out:
CREATE PROCEDURE test @json nvarchar(max) output AS BEGIN SET NOCOUNT ON; --Create table variable declare @t1 as table ([value] nvarchar(max), [ordinal] int); --Fill table INSERT INTO @t1 ([value], [ordinal]) SELECT 'abc', 1 UNION SELECT 'def', 2; --Convert table to JSON set @json = (SELECT * FROM @t1 FOR JSON PATH); END
Then to convert it back to a table, specify the column names as JSON keys:
declare @json nvarchar(max); exec test @json output; --Convert JSON to table SELECT * FROM OPENJSON(@json) WITH ([value] nvarchar(max) '$.value', [ordinal] int '$.ordinal');
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂