存储过程 [英] Stored procedure
问题描述
我是学生，我有以下工作示例麻烦
me（在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
来自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
现在我在aspx页面上用C＃代码调用这个存储过程。
@Firstname参数应该用于
登录到他的客户页面的用户。当客户页面加载他的
orderhistory将通过datareader填充到gridview中。
然而，我不确定是否应该使用OUTPUT或者输入
作为参数。
如果我使用的是数据集，这样可以正常工作，但我想使用
datareader因为我认为资源消耗较少。
因为现在存储过程什么都没有返回，我想知道我在哪里
出错了。
任何小费？
提前谢谢。
Me.Name
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
推荐答案
但是，我不确定是否应该使用OUTPUT或INPUT
However, I''m a little uncertain whether I should use OUTPUT or INPUT
作为参数。
as a parameter.
使用输入参数（默认值）将搜索值提供给
proc。
Use an input parameter (the default) to provide the search value to the
proc.
如果我正在使用数据集，这样可以正常工作，但是我想使用
datareader，因为我认为它消耗的资源更少。
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.
DataReader可用于处理存储过程结果集，而无需更改存储过程代码
。
存储过程有三种基本方式可以返回数据：OUTPUT
参数，结果集（SELECT）和返回码（RETURN）。最佳
练习是仅使用返回代码来表示成功或失败。
OUTPUT参数通常仅在返回标量值时才是选项。
在您的情况下，您需要使用结果集，因为可能会返回许多行
。例如：
CREATE PROCEDURE dbo.CustomersOrderHistory
@Firstname varchar（7）
AS
SELECT ...
WHERE dbo.Customers.Firstname LIKE @Firstname
RETURN @@ ERROR
GO
请注意，参数是通过Command对象访问的，而不是通过
DataReader访问。存储过程返回代码在.Net中显示为
参数，方向为ReturnValue。
-
希望这有助于。
Dan Guzman
SQL Server MVP
" Krij" < gs *** @ start.nowrote in message
news：11 ********************* @ v45g2000cwv.googlegro ups。 com ...
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...
我是学生，我有以下工作示例，麻烦/>
me（在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
来自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
现在我在aspx页面上用C＃代码调用这个存储过程。
@Firstname参数应该用于
登录到他的客户页面的用户。当客户页面加载他的
orderhistory将通过datareader填充到gridview中。
然而，我不确定是否应该使用OUTPUT或者输入
作为参数。
如果我使用的是数据集，这样可以正常工作，但我想使用
datareader因为我认为资源消耗较少。
因为现在存储过程什么都没有返回，我想知道我在哪里
出错了。
任何小费？
提前谢谢。
Me.Name
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
2月20日，14：41，Dan Guzman < guzma ... @nospam-online.sbcglobal.net>
写道：
On 20 Feb, 14:41, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:
但是，我不确定是否应该使用OUTPUT或INPUT
作为参数。
However, I''m a little uncertain whether I should use OUTPUT or INPUT
as a parameter.
使用输入参数（默认值）将搜索值提供给
proc。
Use an input parameter (the default) to provide the search value to the
proc.
如果我正在使用数据集，这样可以正常工作，但是我想使用
datareader，因为我认为它消耗的资源更少。
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.
DataReader可用于处理存储过程结果集，而无需更改存储过程代码
。
存储过程有三种基本方式可以返回数据：OUTPUT
参数，结果集（SELECT）和返回码（RETURN）。最佳
练习是仅使用返回代码来表示成功或失败。
OUTPUT参数通常仅在返回标量值时才是选项。
在您的情况下，您需要使用结果集，因为可能会返回许多行
。例如：
CREATE PROCEDURE dbo.CustomersOrderHistory
@Firstname varchar（7）
AS
SELECT ...
WHERE dbo.Customers.Firstname LIKE @Firstname
RETURN @@ ERROR
GO
请注意，参数是通过Command对象访问的，而不是通过
DataReader访问。存储过程返回代码在.Net中显示为
参数，方向为ReturnValue。
-
希望这有助于。
Dan Guzman
SQL Server MVP
" Krij" < g ... @ start.nowrote in message
news：11 ********************* @ v45g2000cwv .googlegro ups.com ...
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.nowrote in message
news:11*********************@v45g2000cwv.googlegro ups.com...
Hi,
I''ma学生和我有以下工作示例麻烦
me（在SQL Server 2005中）：
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
来自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
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
现在我从C＃代码调用此存储过程aspx页面。
@Firstname参数应该用于
登录到他的客户页面的用户。当客户页面加载他的
时，orderhistory将通过datareader填充到gridview中。
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.
但是，我有点不确定是否应该使用OUTPUT或INPUT
作为参数。
However, I''m a little uncertain whether I should use OUTPUT or INPUT
as a parameter.
如果我正在使用数据集，这可以正常工作，但我想使用
datareader因为我认为资源消耗较少。
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
Me.Name
谢谢： - ）
Thanks :-)
这篇关于存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
