Click here to Skip to main content
15,893,790 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I've written a general procedure execution module for my program in C#.

My procedures support input, and output arguments, and a return value.

How can I mix both the support of returning tables, and returning scalars as OUTPUT arguments at the same time?
Posted
Updated 18-Oct-11 6:26am
v2

1 solution

Well, output arguments are just SqlParameters which are configured to return stuff from a procedure call rather than supply stuff to it. There's a 'Direction' property on them or something like that.

So, get your SqlCommand, add parameters to it for inputs and outputs, then call ExecuteReader on it. That gives you a SqlDataReader which has methods to iterate over multiple result sets. Typically these would be driven by one or more SELECT statements in your proc. Make sense?
 
Share this answer
 
Comments
ShacharK 19-Oct-11 3:44am    
I know the purpose of course of every one of the "components" in the deal.
I just didn't realize how the specific is done... All my SELECTS so far were direct queries, I wanted to have the possibility to combine the two.

You say that I could be having an OUTPUT Parameter that is simply assigned with multiple data? I just couldn't find a good example how to combine that with data selection, could you maybe post some pseudo?
Rob Philpott 19-Oct-11 5:13am    
Going to have to go entirely from memory as unfortunately I'm stuck in an Oracle shop at the moment so syntax might not be quite right, but create a proc something like:

create proc MyProc
(
@a int,
@b int,
@c int out
)
as
set @c = @a
select @a as 'A'
select @b as 'B'
go

So that when you run this, the output parameter c is set to the passed in value a, and two tables come back one with a single column a with a single row in it, and the other with single column b with a single row in it. Then in code, something like:

SqlCommand command = new SqlCommand("MyProc");
command.CommandType = CommmandType.StoredProcedure;
command.Parameters.Add("@a", SqlDbType.Int).Value=10;
command.Parameters.Add("@b", SqlDbType.Int).Value=20;
SqlParameter x = command.Parameters.Add("@c", SqlDbType.Int);
x.Direction = <somethinglike input="">;
SqlDataReader reader = command.ExecuteReader()
Console.WriteLine(x.Value); // should be 10 (I think?)
reader.Read();
Console.WriteLine(reader["a"]);
reader.NextResult(); // move onto the next table
reader.Read();
Console.WriteLine(reader["b"];

Make any sense?
ShacharK 19-Oct-11 23:13pm    
Maybe, I don't know. I don't have a good reason to implement it right away, nothing to do with table-return values right away at this moment. Thank you very much anyway, I'll look at it whenever I would actually need that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900