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

Answer 1

@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.