从 VB.NET 中的存储过程获取返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19053911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting return value from stored procedure in VB.NET
提问by Paks
I have the following stored procedure:
我有以下存储过程：
ALTER PROCEDURE [dbo].[BK_NUMBER]
(@Category NUMERIC(38, 0))
WITH EXECUTE AS CALLER
AS
DECLARE @RunningNumber Int
DECLARE @min Int
DECLARE @max Int
DECLARE @newRunningNumber Int
BEGIN
SELECT @RunningNumber = RunningNumber
FROM PARKMARKENNUMMER
WHERE PARKMARKENTYP_ID = @Category
UPDATE PARKMARKENNUMMER
SET RUNNINGNUMBER = @RunningNumber + 1
WHERE PARKMARKENTYP_ID = @Category
SELECT @newRunningNumber = RunningNumber
FROM PARKMARKENNUMMER
WHERE PARKMARKENTYP_ID = @Category
RETURN @newRunningNumber;
End;
I try to get @newRunningNumberwith this VB code:
我尝试@newRunningNumber使用此 VB 代码：
Dim sqlConnection As New SqlConnection(MSSQL_Helper.strConnectionBookit)
Dim command As New SqlCommand("BK_NUMBER", sqlConnection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@Category", SqlDbType.Int).Value = ID
Dim returnParameter As SqlParameter = command.Parameters.Add("RetVal", SqlDbType.Int)
returnParameter.Direction = ParameterDirection.ReturnValue
sqlConnection.Open()
command.ExecuteNonQuery()
sqlConnection.Close()
Dim returnValue As Integer = returnParameter.Value
Return returnValue
But it returns always "0". What am I doing wrong?
但它总是返回“0”。我究竟做错了什么？
回答by zimdanen
You're not actually returning the value:
您实际上并没有返回值：
RETURN @newRunningNumber
However, as Aaron Bertrandsaid, you should either set it as an OUTPUTparameter or SELECTit back out.
但是，正如Aaron Bertrand所说，您应该将其设置为OUTPUT参数或将SELECT其退出。
OUTPUT:
输出：
ALTER PROCEDURE [dbo].[BK_NUMBER]
(
@Category NUMERIC(38, 0),
@newRunningNumber INT
)
Dim newRunningNumber As SqlParameter("@newRunningNumber", SqlDbType.Int)
newRunningNumber.Direction = ParameterDirection.Output
// execute
Dim returnValue As Integer = newRunningNumber.Value
SELECT:
选择：
SELECT @newRunningNumber AS NewRunningNumber
Dim returnValue As Integer = CType(command.ExecuteScalar(), Integer)
回答by Steve
While I agree with the other posts, OUTPUT parameter is the way to go, most people dont realize that you can put more than a single line into your command text. Because you can, you can convert something a DBA did into something you can use, without changing your stored procedure. For example:
虽然我同意其他帖子，但 OUTPUT 参数是要走的路，大多数人没有意识到您可以在命令文本中放入多行。因为您可以，所以您可以将 DBA 所做的事情转换为您可以使用的内容，而无需更改您的存储过程。例如：
CMD.CommandText = "DECLARE @return_value int;EXEC @return_value = [dbo].[BK_NUMBER] (@Category = " & ID & ";SELECT @return_value"
Dim Ret as int32 = CMD.ExecuteScalar
This is just an example of what you can do. You should of coarse use parameters to avoid injection and have proper error handling, etc etc...
这只是您可以执行的操作的一个示例。您应该粗略使用参数以避免注入并进行适当的错误处理等...
