Output parameter when using storedProcedure

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hello!

In this code snipper I use the storedprocedure RowsAndOutput that is listed
below.
What I find strange in this code is why I have to either call the
rdr.NextResult() or
rdr.Close() before I can read the output parameter called OutputParam from
the storedprocedure

I mean that after I have called while (rdr.Read()) {}
I should be able to access the output parameter from the storedprocedure but
that is not possible becuse it has not any value yet.

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = new SqlCommand("RowsAndOutput", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@OutputParam",
SqlDbType.Int);
param.Direction = ParameterDirection.Output;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{}

object s1 = param.Value;

while (rdr.NextResult())
{}

object s2 = param.Value;
}

ALTER procedure RowsAndOutput (@OutputParam int OUTPUT) AS
select @OutputParam = COUNT(*) from Customers
select customerID, companyName, contactName, phone from Customers

//Tony
 
ALTER procedure RowsAndOutput (@OutputParam int OUTPUT) AS
select @OutputParam = COUNT(*) from Customers
select customerID, companyName, contactName, phone from Customers
Click to expand...

I have no answer for your question other than to say that I avoid SPs with
multiple result sets like the plague.

Wait, I do have another thing to say: why use an output parameter in the
first place? Just do

SELECT COUNT(*) FROM Customers

and read the scalar from the first result set. I only use output parameters
on non-SELECT statements.
 
I know it can be done in the way you say
I just hope to understand why my example behave as it does ?

//Tony
 
In this code snipper I use the storedprocedure RowsAndOutput that is
listed below.
What I find strange in this code is why I have to either call the
rdr.NextResult() or
rdr.Close() before I can read the output parameter called OutputParam
from the storedprocedure

I mean that after I have called while (rdr.Read()) {}
I should be able to access the output parameter from the storedprocedure
but that is not possible becuse it has not any value yet.
Click to expand...

Output parameters are passed, when the statement has completed.
Obviously you statements has not completed as long as one of your
selects are on the way. The server stops execution at the select unless
the client has received the data or, of course, the client cancels the
cursor.


Marcel
 
Marcel Müller said:
Output parameters are passed, when the statement has completed. Obviously
you statements has not completed as long as one of your selects are on the
way. The server stops execution at the select unless the client has
received the data or, of course, the client cancels the cursor.


Marcel
Click to expand...

I don't understand this. I mean if I set a breakpoint on this line object s1
= param.Value;
the select statement is completed so the param.Value should be available but
it isn't.
So why has not the statement completed when I access this line object s1 =
param.Value ?

//Tony
 
I don't understand this. I mean if I set a breakpoint on this line
object s1 = param.Value;
the select statement is completed so the param.Value should be available
but it isn't.
Click to expand...

No, the select has not yet completed. The reader uses deferred
execution. Similar to LINQ. Example:

int[] arr = new int[] {1,2};
int var = 0;
var transformed = arr.Select(i => var = i+1);

So what is the value of var here? It is still 0, because the selection
has not even begun so far.

So why has not the statement completed when I access this line object s1
= param.Value ?
Click to expand...

Lets say your select will return 6GB of data and you are running on a 32
bit platform. If the select has completed, the 6GB data have to be
stored somewhere. In memory is impossible. So what should the database
driver do? Create a temp file? Where?
Furthermore, your application can not start with the processing of the
/first/ result line unless the 6GB have been retrieved over the network,
if it waits for the select to complete.

The DB reader is more like a file stream. It uses some buffering, but it
never holds the entire result set in memory unless it is small.


You always should know that. Think of a database connection pool. If you
fetch a connection from the pool, execute a query, keep the reader open
and then pass the connection back into to pool before the reader has
completed, then you will run into serious trouble. Most database drivers
do not support multiplexed reads over one connection, i.e. you can't
have more than one open reader per connection at a time.


Marcel
 
"Marcel Müller" <[email protected]> skrev i meddelandet











I don't understand this. I mean if I set a breakpoint on this line objects1
= param.Value;
the select statement is completed so the param.Value should be available but
it isn't.
So why has not the statement completed when I access this line object s1 =
param.Value ?

//Tony
Click to expand...

if you put the breakpoint after first DataReader.Read() you will see
in Sql Server Management Studio a Process still executing with your
stored procedure.

For multiple selects in same SP i would use a DataAdapter.Fill so both
selects get retrieved at same time (intead full connected DataReader
behaviour)
 
"bradbury9" <[email protected]> skrev i meddelandet
"Marcel Müller" <[email protected]> skrev i
meddelandet











I don't understand this. I mean if I set a breakpoint on this line object
s1
= param.Value;
the select statement is completed so the param.Value should be available
but
it isn't.
So why has not the statement completed when I access this line object s1 =
param.Value ?

//Tony
Click to expand...

if you put the breakpoint after first DataReader.Read() you will see
in Sql Server Management Studio a Process still executing with your
stored procedure.

For multiple selects in same SP i would use a DataAdapter.Fill so both
selects get retrieved at same time (intead full connected DataReader
behaviour)

Hello!

I just wonder how can I see in Sql Server Management Studio that a process
is still executing my
stored procedure ?
I use Sql Server 2008

//Tony
 
Marcel Müller said:
I don't understand this. I mean if I set a breakpoint on this line
object s1 = param.Value;
the select statement is completed so the param.Value should be available
but it isn't.
Click to expand...

No, the select has not yet completed. The reader uses deferred execution.
Similar to LINQ. Example:

int[] arr = new int[] {1,2};
int var = 0;
var transformed = arr.Select(i => var = i+1);

So what is the value of var here? It is still 0, because the selection has
not even begun so far.

So why has not the statement completed when I access this line object s1
= param.Value ?
Click to expand...

Lets say your select will return 6GB of data and you are running on a 32
bit platform. If the select has completed, the 6GB data have to be stored
somewhere. In memory is impossible. So what should the database driver do?
Create a temp file? Where?
Furthermore, your application can not start with the processing of the
/first/ result line unless the 6GB have been retrieved over the network,
if it waits for the select to complete.

The DB reader is more like a file stream. It uses some buffering, but it
never holds the entire result set in memory unless it is small.


You always should know that. Think of a database connection pool. If you
fetch a connection from the pool, execute a query, keep the reader open
and then pass the connection back into to pool before the reader has
completed, then you will run into serious trouble. Most database drivers
do not support multiplexed reads over one connection, i.e. you can't have
more than one open reader per connection at a time.


Marcel
Click to expand...

I mean when this while{ rdr.Read(){}} return false you have received all the
rows that the stored procedure
have read from the database so because of that the execution of the stored
procedure should end and
you should be able to access the output parameter but still the output
parameter is still null.

So why does not the stored procedure end when I have received every record
from the stored procedure ?

//Tony

//Tony
 
"Marcel Müller" <[email protected]> skrev i meddelandet







No, the select has not yet completed. The reader uses deferred execution.
Similar to LINQ. Example:
Click to expand...
  int[] arr = new int[] {1,2};
  int var = 0;
  var transformed = arr.Select(i => var = i+1);
Click to expand...
So what is the value of var here? It is still 0, because the selection has
not even begun so far.
Click to expand...
Lets say your select will return 6GB of data and you are running on a 32
bit platform. If the select has completed, the 6GB data have to be stored
somewhere. In memory is impossible. So what should the database driver do?
Create a temp file? Where?
Furthermore, your application can not start with the processing of the
/first/ result line unless the 6GB have been retrieved over the network,
if it waits for the select to complete.
Click to expand...
The DB reader is more like a file stream. It uses some buffering, but it
never holds the entire result set in memory unless it is small.
Click to expand...
You always should know that. Think of a database connection pool. If you
fetch a connection from the pool, execute a query, keep the reader open
and then pass the connection back into to pool before the reader has
completed, then you will run into serious trouble. Most database drivers
do not support multiplexed reads over one connection, i.e. you can't have
more than one open reader per connection at a time.
Click to expand...
Marcel
Click to expand...

I mean when this while{ rdr.Read(){}} return false you have received all the
rows that the stored procedure
have read from the database so because of that the execution of the stored
procedure should end and
you should be able to access the output parameter but still the output
parameter is still null.

So why does not the stored procedure end when I have received every record
from the stored procedure ?

//Tony

//Tony
Click to expand...

What Marcel (and me) tried to explain you is that you didnt finish
receiving records from the stored procedure.

SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{}
// Here you have not finished the stored procedure, only first select,
so output vars are bit returned.
object s1 = param.Value;
while (rdr.NextResult())
{}
// Here second select is finished, so stored procedure ends and output
vars are returned
object s2 = param.Value;
}

That happens becouse the DataReader is full connected, to avoid tat
behaviour you should use a DataSet/DataTable that can be used in a
disconnected way. Check the following links for more info:

http://msdn.microsoft.com/en-us/magazine/cc188717.aspx
http://stackoverflow.com/questions/7708347/ado-net-datareaderconnected-vs-datasetdisconnected
 
I mean when this while{ rdr.Read(){}} return false you have received all
the rows that the stored procedure
have read from the database so because of that the execution of the
stored procedure should end and
Click to expand...

A reader is something like an SQL cursor. A cursor can also move
backward in general. So reading the last line is not necessarily an
irreversible process.
Of course, the most frequently used cursors are only for forward
iteration, and in T-SQL you must AFAIK use FOR BROWSE to get a different
cursor type.
So why does not the stored procedure end when I have received every
record from the stored procedure ?
Click to expand...

Because you did not yet close your reader, which frees your SQL cursor.


Marcel
 
Im using spanish version of management studio, so names could be different.

Management -> Activity monitor -> Process info
 
bradbury9 said:
Im using spanish version of management studio, so names could be
different.

Management -> Activity monitor -> Process info
Click to expand...

When I open the Microsoft SQL Server Management Studio I have this menu.
File Edit View Tools Window Community Help

So in which of these can I find the Activity monitor ?

//Tony
 
When I open the Microsoft SQL Server Management Studio I have this menu.
File Edit View Tools Window Community Help

So in which of these can I find the Activity monitor ?
Click to expand...

Right-click the server in the tree. That will work in the 2008 version of
SSMS.
 
Back
Top