从存储过程经典asp返回一个值和一个结果集

2022-01-01 00:00:00 asp-classic sql-server adodb vbscript

我想从经典 ASP 的存储过程中获取返回码和结果集.

I want to get both a return code and a result set back from a stored procedure in classic ASP.

CREATE PROCEDURE CheckEmployeeId
@EmployeeName nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
DECLARE 
      @Exists       INT 
    , @RowCount     Int = 0
    , @ReturnValue  Int = 1


SELECT EmployeeId FROM Employees WHERE Name = @EmployeeName
set @RowCount = @@ROWCOUNT

if (@RowCount <> 1)
BEGIN
    SET @ReturnValue = 2 
END
ELSE
BEGIN
    SET @ReturnValue = 1
END

RETURN @ReturnValue 
END

因此在 ASP 中我可以执行以下操作来获取返回值

So in ASP I can do the following to get the return value

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName")    = EmployeeName
end with
cmd.Execute()
RetVal = cmd.Parameters("@RETURN_VALUE")

或者这个来获取结果集.

or this to get the result set.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName")    = EmployeeName
    Set rst = .Execute()
end with

有没有办法两者兼得?

推荐答案

您已经在做，只需将两者结合即可.

You are already doing it just combine the two.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName") = EmployeeName
    Set rst = .Execute()
end with
'You will need to close the Recordset before returning the RETURN_VALUE.
RetVal = cmd.Parameters("@RETURN_VALUE")

您不需要选择一个或另一个彼此独立.唯一的问题是它们返回的顺序，请记住 OUTPUT 和 RETURN 值在所有返回的 Recordset 关闭之前都无法访问.

You don't need to choose one or the other the are independent of each other. The only issue will be the order they return, remember that both OUTPUT and RETURN values will not be accessible until all returned Recordsets are closed.

就我个人而言，我更喜欢通过将它们存储为二维数组来直接关闭它们.

Personally, I prefer to close them straight away by storing them as 2 Dimensional Arrays.

Set cmd = CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = cnnstr
    .CommandType = adCmdStoredProc
    .CommandText = "CheckEmployeeId"
    .Parameters.Refresh
    .Parameters("@EmployeeName") = EmployeeName
    Set rst = .Execute()
    If Not rst.EOF Then data = rst.GetRows()
    Call rst.Close()
end with
RetVal = cmd.Parameters("@RETURN_VALUE")

'Access Recordset array
If IsArray(data) Then
  'Return first column, first row.
  Response.Write data(0, 0)
End If

相关文章