Output parameter when using storedProcedure

[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

[Jeff Johnson]

"Tony" <johansson...@telia.com> wrote in message
news:4f218cf1$0$281$1472...@news.sunsite.dk...

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

[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

"Jeff Johnson" <I....@enough.spam> skrev i meddelandet
news:jfs815$t7u$1...@dont-email.me...

[Marcel Müller]

On 26.01.2012 18:26, Tony wrote:
> 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

[Tony]

"Marcel Müller" <news.5...@spamgourmet.com> skrev i meddelandet
news:4f21bcb8$0$6628$9b4e...@newsspool2.arcor-online.net...

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

[Marcel Müller]

On 26.01.2012 22:37, Tony wrote:
> 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

[bradbury9]

On 26 ene, 22:37, "Tony" <johansson.anders...@telia.com> wrote:
> "Marcel Müller" <news.5.ma...@spamgourmet.com> skrev i meddelandetnews:4f21bcb8$0$6628$9b4e...@newsspool2.arcor-online.net...

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)

[Tony]

"bradbury9" <ray.br...@gmail.com> skrev i meddelandet
news:762dd9da-50d7-4fc7...@h6g2000yqk.googlegroups.com...

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

[Tony]

"Marcel Müller" <news.5...@spamgourmet.com> skrev i meddelandet

news:4f21d919$0$6622$9b4e...@newsspool2.arcor-online.net...

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

[bradbury9]

On 27 ene, 10:12, "Tony" <johansson.anders...@telia.com> wrote:
> "Marcel Müller" <news.5.ma...@spamgourmet.com> skrev i meddelandetnews:4f21d919$0$6622$9b4e...@newsspool2.arcor-online.net...

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

[Marcel Müller]

On 27.01.2012 10:12, Tony wrote:
> 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

[bradbury9]

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

Management -> Activity monitor -> Process info

[Tony]

"bradbury9" <ray.br...@gmail.com> skrev i meddelandet

news:27903759.1107.1327917562100.JavaMail.geo-discussion-forums@vbto23...

> 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

[bradbury9]

On 1 feb, 00:05, "Tony" <johansson.anders...@telia.com> wrote:
> "bradbury9" <ray.bradbu...@gmail.com> skrev i meddelandetnews:27903759.1107.1327917562100.JavaMail.geo-discussion-forums@vbto23...

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

http://msdn.microsoft.com/en-us/library/ms175518%28v=sql.90%29.aspx

[Jeff Johnson]

"Tony" <johansson...@telia.com> wrote in message

news:4f2873c7$0$291$1472...@news.sunsite.dk...

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

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