Re: SPs which return one value

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Sean Nolan (seann_at_imgno%spaminc.com)
Date: 03/18/05 

Date: Fri, 18 Mar 2005 15:57:48 -0600

Hi Mark,

There's a comparison here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch031.asp

That article goes over much more than just the single value cases you asked
about, but the following paragraph sums it up:
"performance of the ExecuteScalar, Output Parameter, and DataReader
approaches is very similar across the entire range of user load when a
single value is being retrieved.
The ExecuteScalar approach requires less code than the other approaches and
may therefore be the best option from a code maintainability perspective."

I'd agree with that based on personal experience.

Please note three important things:
1. ExecuteScalar always returns the single value that is returned in the
first row and first column of the SQL query, so it is up to you to ensure
that you don't write inefficient code that actually queries lots of rows and
columns - ExecuteScalar will not generate an exception it will just ignore
everything execept the value from the first column and row.
2. You cannot use the integer return value from ExecuteNonQuery because that
is the count of the records affected by your query, it is not a value
returned from your query or sp.
3. You could use the return value from the sp (the value returned by the
RETURN statement in the sp. To do that you need to create a parameter for
your command named @RETURN_VALUE, with a ParameterDirection of
ParameterDirection.ReturnValue. This is an automatic parameter that is
always there. I don't typically use it for returning application values
because it is limited to integer values, but you can use it pretty much like
a regular output parameter, except that you don't have to declare it.
Here's more recommended info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

Sean

"Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:ei1YXT%23KFHA.4092@tk2msftngp13.phx.gbl...
> Hi,
>
> Looking for opinions / advice / tips etc on SQL Server (but I guess this
> would apply to just about any RDBMS) stored procedures which, for whatever
> reason, return only one value. E.g.
>
> 1. INSERTS which return @@IDENTITY
>
> 2. UPDATES which return the number of rows affected by the SP
>
> 3. SELECTS which, say, return a COUNT(*) of rows which satisfy certain
> criteria to prevent duplicate records etc
>
> 4. multi-statement SPs involving transactions which return a 1 or 0 at the
> end to indicate that the transaction was successful and has been
> committed, or was unsuccessful and has been rolled back
>
> In all of the above examples, the SPs could simply return a recordset
> which could be read into an SqlDataReader object, apply the .Read()
> method, and interrogate dr[0].ToString() or whatever. In this case, would
> ExecuteScalar be more efficient than ExecuteReader, even though there will
> only be one row and one column?
>
> Or, would it be more efficient not to return a recordset at all and,
> instead, have the SPs return an int output parameter?
>
> In the case of 2 above, am I correct in thinking that the most efficient
> method would be to interrogate the integer return value of the
> ExecuteNonQuery() method of the Command object?
>
> Any advice gratefully received.
>
> Mark Rae
>

Relevant Pages

  • RE: OracleCommand.ExecuteScalar and PLSQL stored procedures
    ... Think of the ExecuteScalar method as a simple shortcut to get the ... first column of the first row in the resultset returned by the query. ... output/return parameter for a stored procedure call. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Calendar Rendering
    ... > ExecuteScalar method returns a single value (first column, ... which runs a query and gets a SUM value for each day. ... > Dim calCell As TableCell ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: always getting a 0 returned using ExecuteScalar (ms datablocks)
    ... ExecuteScalar: Executes the query, and returns the first column of the ... first row in the result set returned by the query. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SPs which return one value
    ... that returning it as an output parameter is a hair faster. ... > In all of the above examples, the SPs could simply return a recordset ... and interrogate dr.ToStringor whatever. ... > ExecuteScalar be more efficient than ExecuteReader, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Using Stored Procedure at sqlDataAdapters with output parameters
    ... You must create an OUTPUT parameter in the SelectCommand.Parameters ... and use the ExecuteScalar method to return the value. ... I have a Stored Procedure Which Ends with these returns. ... I Create a SqlDataAdapter with a sqlcommand with type stored procedure ...
    (microsoft.public.dotnet.framework.adonet)