HiveBrain v1.2.0
Get Started
← Back to all entries
patternMajor

Table-Valued Parameter as Output parameter for stored procedure

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedtablevaluedoutputprocedureforparameter

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#13782, answer score: 45

Revisions (0)

No revisions yet.