从存储过程经典 asp 返回一个值和一个结果集 [英] Return a value and a result set from stored procedure classic asp

问题描述

我想从经典 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

这篇关于从存储过程经典 asp 返回一个值和一个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！