Output Parameter Returns Nothing

16 Feb 2007 5:40 PM

Sergey Zuyev

Hello!
I'm using SQL Server 2005. I have Stored Procedure that returns Table
Records and RowCount as output parameter:

CREATE PROCEDURE [dbo].[GetListByPage]
@RecordCount bigint OUTPUT
AS
BEGIN
-- get record count
SELECT @RecordCount = (SELECT COUNT(ProductId) FROM [StoreProducts])
-- get table
SELECT * FROM StoreProducts
END

I'm using DAAB to get data from the procedure, but Output parameter returns
nothing.
The only time it returns a result if I don't use second Sql Statement in
procedure that actually returns table. Am I doing something wrong? Any ideas?
Thanks !

Code :
Dim db As Database = DatabaseFactory.CreateDatabase()
'
Dim proc As String = "GetListByPage"
Dim cmd As DbCommand = db.GetStoredProcCommand(proc)
'
db.AddOutParameter(cmd, "@RecordCount", DbType.Int64, 0)

Dim dataReader As IDataReader = db.ExecuteReader(cmd)

Dim o As Object = db.GetParameterValue(cmd, "@RecordCount")

--
Programmer

16 Feb 2007 11:47 PM

William (Bill) Vaughn

That's because ADO.NET expects you to return the rowset first. After having
passed back all of the rows, any OUTPUT parameters are set and passed back
in the TDS. If you add a DataTable.Load (datareader) to read all of the
rows, the output parameter will be populated. But of course, by then you
have the count of the rows anyway (in Table.Rows.Count).

This approach (of counting first) is inefficient and (in this case) simply
doubles the workload on the server. It also is only an approximate guess. If
a row is added or removed between the time you count and the time the last
row is fetched, the count will be off.

This is discussed in detail in my book.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"Sergey Zuyev" <SergeyZu***@discussions.microsoft.com> wrote in message
news:120891E4-CE1A-47A0-B760-FAF6E06683F1@microsoft.com...
> Hello!
> I'm using SQL Server 2005. I have Stored Procedure that returns Table
> Records and RowCount as output parameter:
>
> CREATE PROCEDURE [dbo].[GetListByPage]
> @RecordCount bigint OUTPUT
> AS
> BEGIN
> -- get record count
> SELECT @RecordCount = (SELECT COUNT(ProductId) FROM [StoreProducts])
> -- get table
> SELECT * FROM StoreProducts
> END
>
> I'm using DAAB to get data from the procedure, but Output parameter
> returns
> nothing.
> The only time it returns a result if I don't use second Sql Statement in
> procedure that actually returns table. Am I doing something wrong? Any
> ideas?
> Thanks !
>
> Code :
> Dim db As Database = DatabaseFactory.CreateDatabase()
> '
> Dim proc As String = "GetListByPage"
> Dim cmd As DbCommand = db.GetStoredProcCommand(proc)
> '
> db.AddOutParameter(cmd, "@RecordCount", DbType.Int64, 0)
>
> Dim dataReader As IDataReader = db.ExecuteReader(cmd)
>
> Dim o As Object = db.GetParameterValue(cmd, "@RecordCount")
>
>
>
> --
> Programmer