Stored procedure

[Krij]

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

[Dan Guzman]

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" <gs***@start.nowrote in message
news:11*********************@v45g2000cwv.googlegro ups.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

[Krij]

On 20 Feb, 14:41, "Dan Guzman" <[e-mail]>
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" <[e-mail] in message

news:11*********************@v45g2000cwv.googlegro ups.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 :-)