How to Use Table-Valued Parameter as Output parameter for stored procedure ?

1.5K Asked by AswiniLobo in SQL Server , Asked on Apr 20, 2021

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

Answered by Ankit Chauhan

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

Your Answer

Answer (1)

Ranjana

Using table-valued parameters (TVPs) as output parameters in stored procedures in SQL Server is not directly supported. TVPs are intended for input purposes, allowing you to pass a set of rows into a stored procedure.

However, you can achieve similar functionality by using a temporary table or a table variable within the stored procedure, populating it with the data you want to return, and then selecting from that table. Here's how you can accomplish this:

Step-by-Step Guide

1. Define a Table Type

First, define a table type that can be used as the TVP.

CREATE TYPE MyTableType AS TABLE

  (    Id INT,    Name NVARCHAR(50));

2. Create a Stored Procedure

Create a stored procedure that uses a table variable to store the output data.

  CREATE PROCEDURE MyStoredProcedureASBEGIN    -- Declare a table variable to store the output data    DECLARE @OutputTable MyTableType;    -- Populate the table variable with data    INSERT INTO @OutputTable (Id, Name)    VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');    -- Return the contents of the table variable    SELECT * FROM @OutputTable;END;

3. Call the Stored Procedure

You can call the stored procedure and capture the output in your application or another SQL context.

Example using SQL:

  -- Declare a variable of the table type to store the resultDECLARE @ResultTable MyTableType;-- Insert the result of the stored procedure into the table variableINSERT INTO @ResultTableEXEC MyStoredProcedure;-- Select from the table variable to see the resultSELECT * FROM @ResultTable;

Example using C#:

If you are calling the stored procedure from a C# application, you can use SqlDataAdapter to fill a DataTable with the result.

  using (SqlConnection connection = new SqlConnection("your_connection_string")){    using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection))    {        command.CommandType = CommandType.StoredProcedure;        SqlDataAdapter adapter = new SqlDataAdapter(command);        DataTable resultTable = new DataTable();        adapter.Fill(resultTable);        // Now resultTable contains the data returned from the stored procedure    }}

Summary

While SQL Server does not support table-valued parameters as output parameters directly, you can use table variables or temporary tables within your stored procedure to simulate this behavior. By inserting data into a table variable and selecting from it at the end of your procedure, you can effectively return a set of rows from a stored procedure.

5 Months