Re: Stored Proc works everywhere except delphi - getting weirder

From: hans gulo (hewg_at_removethis.gmx.net)
Date: 11/16/04 

Date: 16 Nov 2004 03:53:56 -0700

Adam Lister wrote:

> I tried that and it unfortunately had no effect.

I have no idea why SET NOCOUNT ON didn't help you.

> and this doesn't:
>
> CREATE PROCEDURE ReadDetails2
> @Status int output
> AS
> Declare @Tmp int
> Select 1 From Dynamo Where CrossCheck = 123456
> Set @Status = 2

Check out this article:
http://msdn.microsoft.com/vstudio/using/understand/data/default.aspx?pul
l=/library/en-us/dnadonet/html/gazoutas.asp

Here I quote a paragraph regarding my previous suggestion.

-- begin quote --
Processing Resultsets

Whenever you execute a stored procedure (and some ad hoc queries), SQL
Server generates one or more resultsets. Don't get this confused with
an RDO Resultset object—it's related but not the same. Each resultset
might contain a rowset and perhaps a packet that contains the
@@ROWCOUNT (rows affected) value. When you execute a SELECT statement
or an action query (INSERT, UPDATE, DELETE), SQL Server sets the
@@ROWCOUNT to indicate the number of rows affected and returns this
value in the last packets sent back to the client. That is, unless you
put SET NOCOUNT ON in your stored procedure, in which case this packet
is not sent. This option makes sense if you don't care about the number
of rows changed by the latest action query. It helps performance too as
ADO does not have to process the extra (empty) resultset.
-- end quote --

Although it talks about ADO.NET, the explaination about SQL Server SP
part still verymuch relevant.

hans 

--

Relevant Pages

  • Re: Returncode von Stored Proc in .NET-Code ermitteln
    ... Die SP gibt ein Resultset zurück, ... Und jedes SELECT sowie INSERT, UPDATE, DELETE liefert ohne SET NOCOUNT ON ... und die Daten hast Du sowieso im DataSet, ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Get Value
    ... you want the proc return value, while you are trying to fetch the first column of the resultset. ... I have following stored Procedure. ... SqlDataReader sqlDr; ... -- SET NOCOUNT ON added to prevent extra result sets from ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SET NOCOUNT ON OPTION
    ... A sp is able to return motre than one resultset. ... > DOES SETTING NOCOUNT ON option affect my stored procedures and website ... > When and when not to set SET NOCOUNT ON? ... > declare @AccountID int ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SET NOCOUNT ON OPTION
    ... So is it good or bad to use SET NOCOUNT ON? ... >A sp is able to return motre than one resultset. ... >> DOES SETTING NOCOUNT ON option affect my stored procedures and website ... >> declare @AccountID int ...
    (microsoft.public.dotnet.framework.aspnet)
  • newbie needs help creating SP
    ... CREATE PROCEDURE spAddGroup ... if @@rowcount> 0 ... I want to get rid of this resultset while keeping SP function the same way. ...
    (microsoft.public.sqlserver.programming)