Here is my current implementation of a stored procedure which returns Order status for a given Order ID. There are two situations,
这是我当前的存储过程实现,它返回给定订单ID的订单状态。有两种情况,
- there is matched Order ID and I will retrieve the related status,
- there is no matched Order ID (i.e. non-existing Order ID).
有匹配的订单ID,我将检索相关的状态,
没有匹配的订单ID(即不存在的订单ID)。
My confusion is, how to implement the two functions elegantly/efficiently in one stored procedure, so that I return matched Order ID for situation 1 and also indicate client no matched Order ID in situation 2?
我的困惑是,如何在一个存储过程中优雅/高效地实现这两个功能,以便我返回情况1的匹配订单ID,并指出客户在情况2中没有匹配的订单ID?
I am using VSTS 2008 + C# + ADO.Net + .Net 3.5 as client, and using SQL Server 2008 as server.
我使用VSTS 2008 + C#+ ADO.Net + .Net 3.5作为客户端,并使用SQL Server 2008作为服务器。
CREATE PROCEDURE [dbo].[GetStatus]
@ID [nvarchar](256),
@Status [int] output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @Status = [Status]
FROM [dbo].[OrderStatus]
WHERE (@ID = [ID]);
END
thanks in advance, George
乔治,提前谢谢
3 个解决方案
#1
why are you using output parameter.
你为什么使用输出参数。
you just need to take your stored procedure result in dataset of the data access layer. just check that if (dataset != null) then take value else return appropriate message to your business layer.
您只需要将存储过程结果存入数据访问层的数据集中。只需检查if(dataset!= null)然后取值,否则返回适当的消息到您的业务层。
#2
There are multiple approaches you can take:
您可以采取多种方法:
-
Keep everything as is and in your .NET code, if the @status value returned is DBNull, then it will indicate situation 2, otherwise situation 1.
按原样和.NET代码保存所有内容,如果返回的@status值为DBNull,则表示情况2,否则情况为1。
-
Add a RETURN statement to the SPROC and use
将一个RETURN语句添加到SPROC并使用
Dim returnValue As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
Dim returnValue As New SqlParameter(“@ RETURN_VALUE”,SqlDbType.Int)
returnValue.Direction = ParameterDirection.ReturnValue
returnValue.Direction = ParameterDirection.ReturnValue
Cmd.Parameters.Add(returnValue)
in your .NET code to explicitly identify what the SPROC returned and take action accordingly.
在您的.NET代码中明确标识SPROC返回的内容并相应地采取措施。
As an additional tip, use a SET instead of SELECT when assigning the value to @Status variable in the SPROC. This will guarantee that you get a NULL back if there is no match found. So,
作为附加提示,在SPROC中将值赋给@Status变量时,请使用SET而不是SELECT。如果找不到匹配项,这将保证您返回NULL。所以,
` -- Insert statements for procedure here
` - 在此插入程序语句
SET @Status = SELECT [Status]
FROM [dbo].[OrderStatus]
WHERE (@ID = [ID]);`
#3
You can use the "if statements" inside the stored procedures. the web site at bottom gives you some tips.
您可以在存储过程中使用“if语句”。底部的网站为您提供了一些提示。
#1
why are you using output parameter.
你为什么使用输出参数。
you just need to take your stored procedure result in dataset of the data access layer. just check that if (dataset != null) then take value else return appropriate message to your business layer.
您只需要将存储过程结果存入数据访问层的数据集中。只需检查if(dataset!= null)然后取值,否则返回适当的消息到您的业务层。
#2
There are multiple approaches you can take:
您可以采取多种方法:
-
Keep everything as is and in your .NET code, if the @status value returned is DBNull, then it will indicate situation 2, otherwise situation 1.
按原样和.NET代码保存所有内容,如果返回的@status值为DBNull,则表示情况2,否则情况为1。
-
Add a RETURN statement to the SPROC and use
将一个RETURN语句添加到SPROC并使用
Dim returnValue As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
Dim returnValue As New SqlParameter(“@ RETURN_VALUE”,SqlDbType.Int)
returnValue.Direction = ParameterDirection.ReturnValue
returnValue.Direction = ParameterDirection.ReturnValue
Cmd.Parameters.Add(returnValue)
in your .NET code to explicitly identify what the SPROC returned and take action accordingly.
在您的.NET代码中明确标识SPROC返回的内容并相应地采取措施。
As an additional tip, use a SET instead of SELECT when assigning the value to @Status variable in the SPROC. This will guarantee that you get a NULL back if there is no match found. So,
作为附加提示,在SPROC中将值赋给@Status变量时,请使用SET而不是SELECT。如果找不到匹配项,这将保证您返回NULL。所以,
` -- Insert statements for procedure here
` - 在此插入程序语句
SET @Status = SELECT [Status]
FROM [dbo].[OrderStatus]
WHERE (@ID = [ID]);`
#3
You can use the "if statements" inside the stored procedures. the web site at bottom gives you some tips.
您可以在存储过程中使用“if语句”。底部的网站为您提供了一些提示。