How to return an array from stored procedure?

Question

I need to return an array from stored procedure. And at client side,finally need to retrieve the data by using c# and ado.net.

CREATE PROCEDURE [dbo].[usp_testing]

AS
BEGIN

DECLARE @ARRAY TABLE(sno int,id nvarchar(50)

INSERT @ARRAY(sno,id) values(1,'v123')
INSERT @ARRAY(sno,id) values(2,'v124')
INSERT @ARRAY(sno,id) values(3,'v124')
END

This @Array table need to be returned and to be retrieved to the client side

And what have you tried? What specific difficulties did you encounter? — Darin Dimitrov
– Darin Dimitrov, Commented Aug 31, 2012 at 9:52
you can use XML as a output parameter or use table value function in this situation. — sganesh
– sganesh, Commented Aug 31, 2012 at 9:56
i am trying to return this array as return @array but its not doing — Aada
– Aada, Commented Aug 31, 2012 at 9:57

Marc Gravell · Accepted Answer · 2012-08-31 09:56:58Z

7

@ARRAY is not an array: it is a table. The mechanism for obtaining a table is simple and well-practiced:

select * from @ARRAY

To get that at the client, use either ExecuteReader or something like a DataTable if it makes you happy. Or something like dapper:

var arr = connection.Query(
    "usp_testing", commandType: CommandType.StoredProcedure)
    .Select(row => Tuple.Create((int)row.sno, (string)row.id)).ToArray();

which is an array of tuples.

answered Aug 31, 2012 at 9:56

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yván Ecarri · Accepted Answer · 2012-08-31 10:05:13Z

2

create procedure strange_procedure as
begin

DECLARE @ARRAY TABLE(id int not null identity primary key, value varchar(50))

insert into @ARRAY (value) values ('This value')

select  *  from @ARRAY


end

answered Aug 31, 2012 at 10:05

Yván Ecarri

1,76019 silver badges42 bronze badges

Collectives™ on Stack Overflow

How to return an array from stored procedure?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related