CODEWITHSUNDEEP
c#sqlstored-procedures
Karl
Karl

Reputation: 5822

Controlling SQL stored procedure multiple outputs in C#

Can anyone tell me how I can control the output from an SQL stored procedure that returns more than one set of output?

I am currently doing the following:

        DataTable allData = new DataTable();
        SqlConnection connection = new SqlConnection(mySource);

        SqlCommand cmd = new SqlCommand(procedureName, connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(paramName, SqlDbType.Int);
        cmd.Parameters[paramName].Value = paramValue;

        connection.Open();

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        adapter.Fill(allData);

        connection.Close();

Now this works fine if the procedure has only one output value, but how do I deal with the following:

My stored procedure broadly does the following:

It calls a number of other stored procedures in order to construct a dynamic SQL query (lets call this @query) and then calls EXECUTE(@query) which does a SELECT.

Using the code snippet above returns the result from the SELECT query, which is fine. But what I would also like it so have the string @query returned. I can specify it as an output type and let SQL fetch it, but how do I access it from c#? (Actually, more specifically, when I do this the code snipped above returns only the string @query and no longer returns the results of the SELECT)

Thanks

Karl

Upvotes: 1

Views: 2207

Answers (2)

IordanTanev
IordanTanev

Reputation: 6240

You can do like this:

 DataSet allData = new DataSet ();
 ...
 ...
 ...
  adapter.Fill(allData);

then each result of the select is in different dataTable

Upvotes: 3

Dewfy
Dewfy

Reputation: 23624

Using SqlDataReader.NextResult . This little bit shifts you from using SqlDataAdapter, but you still is able to populate DataTable with DataTable.Load

Upvotes: 3

Related Questions