Posted by Dan Guzman on 02/20/07 13:41

> However, I'm a little uncertain whether I should use OUTPUT or INPUT
> as a parameter.

Use an input parameter (the default) to provide the search value to the
proc.

> This works fine if I'm using a dataset, but I'd like to use a
> datareader because I think it is less consuming of resources.

A DataReader can be used to process the stored procedure result set without
changing the stored procedure code.

There are 3 basic ways that a stored procedure can return data: OUTPUT
parameter, result set (SELECT) and the return code (RETURN). The Best
Practice is to use the return code only to indicate success or failure.
OUTPUT parameters are typically an option only when scalar values returned.
In your case, you need to use a result set because many rows can be
potentially returned. For example:

CREATE PROCEDURE dbo.CustomersOrderHistory
@Firstname varchar(7)
AS

SELECT ...
WHERE dbo.Customers.Firstname LIKE @Firstname

RETURN @@ERROR
GO

Note that parameters are accessed via the Command object rather than a
DataReader. The stored procedure return code is exposed in .Net as a
parameter with direction ReturnValue.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krij" <gsb58@start.no> wrote in message
news:1171951004.077395.42310@v45g2000cwv.googlegroups.com...
> Hi,
>
> I'm a student and I have the following working example that troubles
> me (in SQL Server 2005):
>
> CREATE PROCEDURE dbo.CustomersOrderHistory
> ( @Firstname varchar(7) OUTPUT)
> AS
> SELECT dbo.Customers.Firstname, dbo.Orders.Orderdate,
> dbo.Orderdetails.Orderamount, dbo.Orderdetails.Orderprice,
> dbo.Orderdetails.Orderdiscount,
> dbo.Items.Itemname,dbo.Orderdetails.Orderamount
> * dbo.Orderdetails.Orderprice AS Ordersum
> FROM dbo.Orderdetails INNER JOIN
> dbo.Items ON dbo.Orderdetails.ItemID =
> dbo.Items.ItemID INNER JOIN
> dbo.Orders ON dbo.Orderdetails.OrderID =
> dbo.Orders.OrderID INNER JOIN
> dbo.Customers ON dbo.Orders.Customer =
> dbo.Customers.CustomerID
> WHERE dbo.Customers.Firstname LIKE @Firstname
> ORDER BY dbo.Orders.Orderdate
> /* SET NOCOUNT ON */
> RETURN @Firstname
>
> Now I'm calling this stored procedure from C# code on an aspx page.
> The @Firstname parameter is supposed to be used against the user who
> logges in to his customerpage. When the customer page loads his
> orderhistory will be filled into a gridview through a datareader.
>
> However, I'm a little uncertain whether I should use OUTPUT or INPUT
> as a parameter.
>
> This works fine if I'm using a dataset, but I'd like to use a
> datareader because I think it is less consuming of resources.
>
> As it is now the stored procedure return nothing and I wonder where I
> go wrong.
>
> Any tip?
>
> Thank you in advance.
>
> Me.Name
>

[Back to original message]