Re: SqlCommand.ExecuteReader does not populate Output Parmeters?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It is stupid and you don't understand the issue. This has nothing do with the return of the @@ROWCOUNT before teh rcord set has completed.

sample proc:


create proc test
    @numrows int output
AS
SET NOCOUNT ON

select identity(1,1) rownum,* into #tmp from mytable

SET @numrows =  @@rowcount


select * from #tmp
where rownum between 11 and 20




Typical of a paged return. The issue here has nothing to do with SQL server the issue is that the DataReader interface is not reading the output parameter until after the recordset has been paged through. No reason for it. Stupid.






William (Bill) Vaughn wrote:
Ah, SQL Server is one of the fastest (if not the fastest), most respected and most sophisticated DBMS systems on the planet. This behavior (as stupid as you might think) has been in place since the first implementation of stored procedure OUTPUT parameters. Just because it does not work the way you want it does not make it stupid. The problem is that setting an OUTPUT parameter to a value like @@ROWCOUNT is meaningless. Consider that SQL Server does not know how many rows qualify for the most recent query until all the rows are fetched. Since SQL Server is designed to work with hundreds to thousands of users a second, the data is constantly in flux. As rows are added, changed, removed and moved around in the DBMS, the engine won't (can't) know how many rows will eventually be sent to the client until it has completed the process of retrieving and transmitting the rows to the client. SQL Server does not send any rows to the client until your application actually asks for the rows. This means your application must retrieve each and every row fetched from the server before SQL Server can compute the @@ROWCOUNT. That's one reason why OUTPUT parameters are sent last.


.

Relevant Pages

  • Re: SqlCommand.ExecuteReader does not populate Output Parmeters?
    ... He still thinks it's stupid. ... > SQL Server executes statements and how that information flows between the ... > client and the server. ... > verify that output parameters really come at the end of the results at the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Upsizing issue
    ... >>> half the application because of stupid Recordset problem. ... You could link the SQL Server ... magnitude as modifying the original ASP pages. ...
    (microsoft.public.inetserver.asp.db)
  • Re: SqlCommand.ExecuteReader does not populate Output Parmeters?
    ... Ah, SQL Server is one of the fastest, most respected ... and most sophisticated DBMS systems on the planet. ... you want it does not make it stupid. ... That's one reason why OUTPUT parameters are sent ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: index bloat?
    ... I also feel kind of stupid that I did not think of this earlier. ... That time I could just tell our helpdesk folks "try this". ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)