Output parameter when using storedProcedure

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
 
J

Jeff Johnson

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

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

Tony

I know it can be done in the way you say
I just hope to understand why my example behave as it does ?

//Tony
 
M

Marcel Müller

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.

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
 
T

Tony

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

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
 
M

Marcel Müller

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.

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 ?

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
 
B

bradbury9

"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

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)
 
T

Tony

"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

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
 
T

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.

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 ?

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

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
 
B

bradbury9

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







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

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

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
 
M

Marcel Müller

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

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 ?

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


Marcel
 
B

bradbury9

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

Management -> Activity monitor -> Process info
 
T

Tony

bradbury9 said:
Im using spanish version of management studio, so names could be
different.

Management -> Activity monitor -> Process info

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
 
J

Jeff Johnson

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 ?

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top