|
|
Posted by Krij on 02/21/07 07:19
On 20 Feb, 14:41, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:
> > 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" <g...@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
Thanks :-)
Navigation:
[Reply to this message]
|