Stored Procedures that return both output parameters and table results

[thomasbihn]

I'm a newbie to C# development and as a whole, my department has been trying to come over from the VB6 realm. In VB6, I use calls to stored procedures on SQL Servers to return any combination of output. I've learned how to return a data set and output parameters seperately, but need to learn how to get the data on the same round-trip call to the stored procedure.

I can't have it execute the command for a result set, then execute the command again to get the output parameter because database tables are normally updated with each call to the stored procedure. One example is getting a table of information and at the same time, returning a new serial number that the stored procedure generated and logged. The table data is related to that serial number.

I can't afford to rewrite all the stored procedures and also add network bandwidth by making additional calls to get the same information I'm used to getting in the same round-trip call to the database server.

Any help or references would be greatly appreciated. My searches only yield one or the other.

[Plater]

I think you would just be able to combine them?
For example:

//this procedure returns 4 tables and sets @OutTest to a value
SqlCommand sc = new SqlCommand("GetShippingResultsForDate", dbcon);
sc.CommandType = CommandType.StoredProcedure;
 
sc.Parameters.AddWithValue("@WhatDay", WhatDay);
sc.Parameters.Add("@OutTest", SqlDbType.Int);
sc.Parameters["@OutTest"].Direction = ParameterDirection.Output;
 
SqlDataAdapter dba = new SqlDataAdapter(sc);
dba.Fill(retval);
object o=sc.Parameters["@OutTest"].Value;
//o is now an int containing the value I set in my stored procedure