Table-Valued Parameter as Output parameter for stored procedure

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

[{"value":"abc","ordinal":1},{"value":"def","ordinal":2}]

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');