PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 5.00 average.

Output parameter when using storedProcedure

 
 
Tony
Guest
Posts: n/a
 
      26th Jan 2012
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

 
Reply With Quote
 
 
 
 
Jeff Johnson
Guest
Posts: n/a
 
      26th Jan 2012
"Tony" <(E-Mail Removed)> wrote in message
news:4f218cf1$0$281$(E-Mail Removed)...

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


 
Reply With Quote
 
 
 
 
Tony
Guest
Posts: n/a
 
      26th Jan 2012
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" <(E-Mail Removed)> skrev i meddelandet
news:jfs815$t7u$(E-Mail Removed)...
> "Tony" <(E-Mail Removed)> wrote in message
> news:4f218cf1$0$281$(E-Mail Removed)...
>
>> 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.
>


 
Reply With Quote
 
Marcel Müller
Guest
Posts: n/a
 
      26th Jan 2012
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
 
Reply With Quote
 
Tony
Guest
Posts: n/a
 
      26th Jan 2012

"Marcel Müller" <(E-Mail Removed)> skrev i meddelandet
news:4f21bcb8$0$6628$(E-Mail Removed)-online.net...
> 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


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

 
Reply With Quote
 
Marcel Müller
Guest
Posts: n/a
 
      26th Jan 2012
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
 
Reply With Quote
 
bradbury9
Guest
Posts: n/a
 
      27th Jan 2012
On 26 ene, 22:37, "Tony" <(E-Mail Removed)> wrote:
> "Marcel Müller" <(E-Mail Removed)> skrev i meddelandetnews:4f21bcb8$0$6628$(E-Mail Removed)-online.net...
>
>
>
>
>
>
>
>
>
> > 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

>
> 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)
 
Reply With Quote
 
Tony
Guest
Posts: n/a
 
      27th Jan 2012

"bradbury9" <(E-Mail Removed)> skrev i meddelandet
news:(E-Mail Removed)...
On 26 ene, 22:37, "Tony" <(E-Mail Removed)> wrote:
> "Marcel Müller" <(E-Mail Removed)> skrev i
> meddelandetnews:4f21bcb8$0$6628$(E-Mail Removed)-online.net...
>
>
>
>
>
>
>
>
>
> > 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

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


 
Reply With Quote
 
Tony
Guest
Posts: n/a
 
      27th Jan 2012

"Marcel Müller" <(E-Mail Removed)> skrev i meddelandet
news:4f21d919$0$6622$(E-Mail Removed)-online.net...
> 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


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



 
Reply With Quote
 
bradbury9
Guest
Posts: n/a
 
      27th Jan 2012
On 27 ene, 10:12, "Tony" <(E-Mail Removed)> wrote:
> "Marcel Müller" <(E-Mail Removed)> skrev i meddelandetnews:4f21d919$0$6622$(E-Mail Removed)-online.net...
>
>
>
>
>
>
>
>
>
> > 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

>
> 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/7...etdisconnected
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enterprise Library Logging using custom storedprocedure nick_tucker@hotmail.com Microsoft C# .NET 0 16th Oct 2006 09:08 PM
output data from the result of storedprocedure into excel format =?Utf-8?B?VGhhbmtz?= Microsoft VB .NET 2 24th May 2006 07:12 AM
error STOP:0x0000007B (parameter, parameter, parameter, parameter) robert35 Microsoft Access Getting Started 1 15th Dec 2004 04:28 PM
Typed Dataset using storedprocedure that contains temporary tables Microsoft ADO .NET 1 3rd Nov 2004 10:17 PM
Re: CommandType.StoredProcedure vs. CommandType.Text bruce barker Microsoft ADO .NET 0 22nd Jul 2003 08:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:18 AM.