returning data from a stored procedure

[Mike P]

If you have a stored procedure that may return one row or many rows, but
you only actually want the first row, should you use a datareader or
dataset, or will just reading the output parameters give you the first
row?

CREATE PROCEDURE CCFindData
(
@sessionid bigint output,
@purchaseid bigint output
)

AS
select @sessionid = sessionid, @purchaseid = purchaseid
from attemptedpurchase
where status = 0
GO


DResult drFindData;
lngCurrentSession = 0;
lngCurrentID = 0;

SqlConnection objConnection = GetConnection(strConnectMcCallumTest);
SqlCommand objCommand = new SqlCommand("CCFindData", objConnection);

objCommand.CommandType = CommandType.StoredProcedure;

SqlParameter prmSessionID = new SqlParameter("@SessionID",
SqlDbType.BigInt, 8);
prmSessionID.Direction = ParameterDirection.Output;
objCommand.Parameters.Add(prmSessionID);

SqlParameter prmPurchaseID = new SqlParameter("@PurchaseID",
SqlDbType.BigInt, 8);
prmPurchaseID.Direction = ParameterDirection.Output;
objCommand.Parameters.Add(prmPurchaseID);


try
{
objConnection.Open();
objCommand.ExecuteNonQuery();
objConnection.Close();

if (prmSessionID.Value != System.DBNull.Value)
{
strPurchaseID = Convert.ToString(prmPurchaseID.Value);
strSessionID = Convert.ToString(prmSessionID.Value); drFindData =
DResult.Valid;
}
else
{
drFindData = DResult.Invalid;
}
}
catch
{
drFindData = DResult.Error;
}


Cheers,

Mike

 

[Richard Blewett [DevelopMentor]]

That stored procedure won't return any rows, it only returns the out params.

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

nntp://news.microsoft.com/microsoft.public.dotnet.languages.csharp/<[email protected]>

If you have a stored procedure that may return one row or many rows, but
you only actually want the first row, should you use a datareader or
dataset, or will just reading the output parameters give you the first
row?

CREATE PROCEDURE CCFindData
(
@sessionid bigint output,
@purchaseid bigint output
)

AS
select @sessionid = sessionid, @purchaseid = purchaseid
from attemptedpurchase
where status = 0
GO

 

[Mike P]

That's what I meant, just the out parameters. I've tried this code and
it always seems to return the LAST set of output parameters that meet
this condition. I suppose I could get around this by changing my stored
procedure to order records in descending order, but I'm guessing this is
not the best way to do this?


Mike

 

[Richard Blewett [DevelopMentor]]

select top 1 @sessionid = sessionid, @purchaseid = purchaseid
from attemptedpurchase
where status = 0

does that do what you want?

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

nntp://news.microsoft.com/microsoft.public.dotnet.languages.csharp/<[email protected]>

That's what I meant, just the out parameters. I've tried this code and
it always seems to return the LAST set of output parameters that meet
this condition. I suppose I could get around this by changing my stored
procedure to order records in descending order, but I'm guessing this is
not the best way to do this?


Mike

 

[Mike P]

Thanks Richard!


Cheers,

Mike