Prev: Person's age based on DOB
Next: Conditional UNION
From: LenaMsdn08 on 27 May 2010 16:06
I need a stored procedure that returns a recordset and a return value, for
example, (oversimplified, of course)

CREATE PROC test_proc
as
begin
set nocount on
declare @err int
select * from customers
select @err = @@ERROR
set nocount off
return @err
go

The stored procedure will be called from an ASP.Net page using
SqlCommand.ExecuteReader. I understand that the return value will not be
available (through an SqlParameter with Direction = ReturnValue) until after
reading all the records from the datareader and closing the reader.

If I don't want to go through all records in the DataReader, what are the
correct steps to close the reader early and be certain that the return value
is available? According to this article
http://msdn.microsoft.com/en-us/library/ms971497.aspx#gazoutas_topic4, it is
not sufficient to just close the datareader.

I have looked at SqlCommand.Cancel but it does not appear to return whether
it succeeded or failed ... so after calling Cancel, do I know if the return
value is actually available?

Is there a better way to do this?

Thank you!
Lena
From: Erland Sommarskog on 27 May 2010 18:13
LenaMsdn08 (LenaMsdn08(a)newsgroup.nospam) writes:
> The stored procedure will be called from an ASP.Net page using
> SqlCommand.ExecuteReader. I understand that the return value will not be
> available (through an SqlParameter with Direction = ReturnValue) until
> after reading all the records from the datareader and closing the
> reader.
>
> If I don't want to go through all records in the DataReader, what are
> the correct steps to close the reader early and be certain that the
> return value is available? According to this article
> http://msdn.microsoft.com/en-us/library/ms971497.aspx#gazoutas_topic4,
> it is not sufficient to just close the datareader.

Call .NextResult until it returns NULL.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Person's age based on DOB
Next: Conditional UNION